A MySQL Joins clause is used to combine rows from two or more tables, based on a common field between them.
There are different types of joins available in MySQL:
INNER JOIN: returns rows when there is a match in both tables.
LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN: returns rows when there is a match in one of the tables.
SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.
We can take each first four joins in Detail:
We have two tables with the following values.
TableA
id | firstName | lastName |
---|---|---|
1 | John | Doe |
2 | Steve | Jobs |
3 | Elon | Musk |
TableB
id2 | age | place |
---|---|---|
1 | 24 | UK |
2 | 26 | USA |
3 | 34 | China |
INNER JOIN
Note: it gives the intersection of the two tables, i.e. rows they have common in TableA and TableB
Syntax
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;
Apply it in our sample table :
SELECT TableA.firstName, TableA.lastName, TableB.age,TableB.Place FROM TableA INNER JOIN TableB ON TableA.id = TableB.id2;
Result Will Be
firstName | lastName | age | place |
---|---|---|---|
John | Doe | 24 | UK |
Steve | Jobs | 26 | USA |
Elon | Musk | 34 | China |
LEFT JOIN
Note: Left Join will give all selected rows in TableA, plus any commonly selected rows in TableB.
Syntax
SELECT table1.column1, table2.column2... FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field;
Apply it in our sample table :
SELECT TableA.firstName, TableA.lastName, TableB.age,TableB.Place FROM TableA LEFT JOIN TableB ON TableA.id = TableB.id2;
Result
firstName lastName age Place ……………………………………………………………………. arun prasanth 24 Kerala ann antony 24 usa Sruthy abc 25 km new ABC NULL NULL
RIGHT JOIN
Note: will give all selected rows in TableB, plus any commonly selected rows in TableA.
Syntax
SELECT table1.column1, table2.column2... FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field;
Apply it in our sample table :
SELECT TableA.firstName, TableA.lastName, TableB.age,TableB.Place FROM TableA RIGHT JOIN TableB ON TableA.id = TableB.id2;
Result
firstName lastName age Place ……………………………………………………………………. arun prasanth 24 Kerala ann antony 24 usa Sruthy abc 25 km NULL NULL 24 chennai
FULL JOIN
Note: It is the same as a union operation, it will return all selected values from both tables.
Syntax
SELECT table1.column1, table2.column2... FROM table1 FULL JOIN table2 ON table1.common_field = table2.common_field;
Apply it in our sample table :
SELECT TableA.firstName, TableA.lastName, TableB.age, TableB.Place FROM TableA FULL JOIN TableB ON TableA.id = TableB.id2;
Result
firstName lastName age Place ……………………………………………………………………. arun prasanth 24 Kerala ann antony 24 usa Sruthy abc 25 km new ABC NULL NULL NULL NULL 24 Chennai
Interesting Facts:
For INNER joins the order doesn’t matter
For (LEFT, RIGHT, or FULL) OUTER joins, the order matter