Blog

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

TableB

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

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

Read More on MySQL Joins.