MySQL SELF JOIN

In SQL, SELF JOIN is a join of a table with itself. This type of query is not so common but very practical in the case where a table links information with records of the same table.

 
 

 

Syntax :

To perform a SELF JOIN, the syntax of the SQL query is as follows:

SELECT `t1`.`column_name1`, `t1`.`column_name2`, `t2`.`column_name1`, `t2`.`column_name2`
FROM `table` as `t1`
LEFT OUTER JOIN `table` as `t2` ON `t2`.`fk_id` = `t1`.`id`

Here the join is performed with a LEFT JOIN, but it is also possible to perform it with other types of joins.
 

Example :

A possible example could be a corporate intranet application that has a table of employees with the hierarchy between them. The employees can be managed by a direct superior who is himself in the table.
 
Employee table:

+--------+-----------+------+-------------------------------+------------+
| UserID |    Name   |  Age |             Address           | manager_id |
+--------+-----------+------+-------------------------------+------------+
|   1    |    Alex   |  25  | 819 Saint Francis Way         |  NULL      |
|   2    |   Emily   |  15  | 171 Jarvisville Road Michigan |  NULL      |
|   3    |   Jean    |  35  | 188 Clay Street Indiana       |  1         |
|   4    |    Bob    |  40  | 285 Java Lane Missouri        |  2         |
+--------+-----------+------+-------------------------------+------------+

The above table shows some employees. The first employees do not have a superior, while employees n°3 and n°4 have respectively employee n°1 and employee n°2 as their superior.
 

 
It is possible to list on the same line the employees with their direct superiors, using a query like this one:

SELECT e1.UserID, e1.Name, e2.UserID, e2.Name
FROM `Employee` as `e1`
LEFT OUTER JOIN Employee as `e2` ON e2.manager_id = e1.UserID

Output:

+-----------+-----------+-----------+-----------+
| e1.UserID |  e1.Name  | e2.UserID |  e2.Name  |
+-----------+-----------+-----------+-----------+
|    1      |   Alex    |    NULL   |  NULL     |
|    2      |   Emily   |    NULL   |  NULL     |
|    3      |   Jean    |    1      |  Alex     |
|    4      |   Bob     |    2      |  Emily    |
+-----------+-----------+-----------+-----------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More