MySQL JOIN Keyword
The MySQL JOIN keyword is used to combine rows of two or more tables based on common column between them. There are four types of JOINs in MySQL:
- INNER JOIN: It is sometimes called simple JOIN. It returns records based on matching rows in both tables.
- LEFT JOIN: It is sometimes called LEFT OUTER JOIN. It returns records which contains all rows from left table and matching rows from right tables.
- RIGHT JOIN: It is sometimes called RIGHT OUTER JOIN. It returns records which contains all rows from right table and matching rows from left tables.
- CROSS JOIN: Returns records which contains all rows from both tables. It is sometimes called CARTESIAN JOIN because in the absence of a WHERE condition it behaves like a CARTESIAN PRODUCT i.e., the number of rows in the result-set is the product of the number of rows of the two tables.
Example:
Consider a database containing tables called Employee and Contact_Info with the following records:
Table 1: Employee table
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
Table 2: Contact_Info table
Phone_Number | EmpID | Address | Gender |
---|---|---|---|
+1-8054098000 | 2 | Brooklyn, New York, USA | F |
+33-147996101 | 3 | Grenelle, Paris, France | M |
+31-201150319 | 4 | Geuzenveld, Amsterdam, Netherlands | F |
+86-1099732458 | 6 | Yizhuangzhen, Beijing, China | M |
+65-67234824 | 7 | Yishun, Singapore | M |
+81-357799072 | 8 | Koto City, Tokyo, Japan | M |
-
The JOIN keyword is used with Employee and Contact_Info tables based on common column EmpID. It returns Name, Age and Address columns based on match in both tables. The query is given below:
SELECT Employee.Name, Employee.Age, Contact_Info.Address FROM Employee JOIN Contact_Info ON Employee.EmpID = Contact_Info.EmpID;
This will produce the result as shown below:
Name Age Address Marry 24 Brooklyn, New York, USA Jo 27 Grenelle, Paris, France Kim 30 Geuzenveld, Amsterdam, Netherlands Huang 28 Yizhuangzhen, Beijing, China
❮ MySQL Keywords