MySQL Self Join

The self join is used to join a table to itself when using a join.

A self join is useful for when you want to combine records in a table with other records in the same table that match a certain join condition.

Consider the following example:

SELECT 

a.customer_id, 

a.first_name, 

a.last_name, 

   b.customer_id,

b.first_name, 

b.last_name 

FROM customer a

INNER JOIN customer b

ON a.last_name = b.first_name;

Result:

In the above example, we retrieve all customers whose last name matches the first name of another customer. We achieve this by assigning aliases to the customer table while performing an inner join on the two aliases. The aliases allow us to join the table to itself because they give the table two unique names, which means that we can query the table as though it was two different tables.

Using a LEFT JOIN

Self joins aren't limited to the INNER JOIN. You can also use a LEFT JOIN to provide all records from the left "table" regardless of whether there's a match on the right one.

SELECT 

a.customer_id, 

a.first_name, 

a.last_name, 

   b.customer_id,

b.first_name, 

b.last_name 

FROM customer a

LEFT JOIN customer b

ON a.last_name = b.first_name

ORDER BY a.customer_id;

Result:

Using a RIGHT JOIN

And of course, you can also use a RIGHT JOIN to provide all records from the right "table" regardless of whether there's a match on the left one.

SELECT 

a.customer_id, 

a.first_name, 

a.last_name, 

   b.customer_id,

b.first_name, 

b.last_name 

FROM customer a

RIGHT JOIN customer b

ON a.last_name = b.first_name

ORDER BY b.first_name;

Result:

Note that I changed the ORDER BY clause to use b.first_name purely for demonstration purposes. By doing this, I was able to get some matches near the top of the results and into the screenshot.