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:

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.

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.

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.

The examples on this page use the Sakila sample database.