MySQL – Left, Right, Outer or Inner Join? No – the SELF JOIN

Hi to all of you! Today, I’m going to explain SELF JOIN in MySQL – I’ll answer why and then how should you use it.

But first, a short explanation. I’m going to explain a non often used SQL that came in hand recently. If you are still learning MySQL, please refer to some basics first – a good start. And now, to the fun part. 😀

Why do anyone need SELF JOIN?

Well, SQL self-join is very useful when you want to retrieve related data stored in one table such as organizational structure. Its basically an inner join with reference on the table itself.

And a practical example of usage:

In our DB sample, we have employees table which stores not only employee data but also organizational structure.

SQL for sample table:

CREATE TABLE `test`.`employees` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 50 ) NOT NULL ,
`title` VARCHAR( 50 ) NOT NULL ,
`superior` INT( 11 ) NOT NULL
) ENGINE = MYISAM ;

And SQL for sample values:

INSERT INTO `employees` (id,name,title,superior)
VALUES
(NULL,"Name 1","Junior developer 1",3),
(NULL,"Name 2","Junior developer 2",3),
(NULL,"Name 3","Senior developer 3",4),
(NULL,"Name 4","Project manager",0);

And finally, self join would look like:

SELECT
B.name employee, B.title employee_title, A.name superior, A.title superior_title
FROM employees AS A
INNER JOIN employees AS B ON A.id = B.superior

The result of this query with this sample database table, and this dataset (that we inserted) would produce a result like this:

This table should be self-explanatory but if you aren’t sure what just happened, please do experiment with your sample database table.

Note: You can see that there’s no “SELF JOIN” syntax but rather inner join on same table.

I hope this article helped you!