/    /  MySQL – SELF JOIN

MySQL – SELF JOIN

 

MySQL - SELF JOIN

SELF JOINS are used to join two tables within a single table. In the previous sections, we learned how to join a table with other tables using INNER, LEFT, RIGHT, and CROSS JOINS. However, some data needs to be combined with data from other tables within the same table. We use Self Join in such a case.

Syntax

As with joining two different tables, the syntax of a self-join is the same. In this case, we are using aliases for the names of the tables since they are the same. SELF JOIN syntax in MySQL is as follows:

SELECT c1.col_name, c2.col_name...  
FROM table1 c1, table1 c2  
WHERE c1.common_col_name = c2.common_col_name; 

Example

SELECT e1.first_name, e1.last_name, e2.emp_no
FROM employees e1, dept_emp e2
WHERE e1.emp_no = e2.emp_no;

MySQL - SELF JOIN

In addition, the JOIN keyword may be used instead of a comma in order to join tables

SELECT e1.first_name, e1.last_name,e2.emp_no
FROM employees e1
JOIN employees e2 ON e1.emp_no = e2.emp_no;

MySQL - SELF JOIN