What is a SELF JOIN?

There are times when it is helpful to join two or more tables together.

There’s an option to join a table to itself. It’s called the self-join and it’s used to look at the same set of JOIN is a common term in SQL, and using it is pretty straight forward.

This sentence illustrates how to join a table to itself

SELECT
	column1,
	column2,
	column3,
        ...
FROM
	table1 A
INNER JOIN table1 B ON B.column1 = A.column2;

The join situation of table1 was assigned by statement1 to table2. The alias columns are table1.column1 and table1.column2 

You can also use the LEFT JOIN clause for the INNER JOIN clause. Let’s take a few examples of self-joining.

SQL SELF JOIN examples

See the staff table mentioned below

employees
*employee_id
first_name
last_name
email
phone_number
hire_date
job_id
salary
manager_id
department_id

A full-time employee’s manager is indicated by the manager_id column in the employee table. The information of who reports to whom is retrieved from the employees table by the following statement.

SELECT 
    e.first_name || ' ' || e.last_name AS employee,
    m.first_name || ' ' || m.last_name AS manager
FROM
    employees e
        INNER JOIN
    employees m ON m.employee_id = e.manager_id
ORDER BY manager;
Try It

The president’s row in the table doesn’t have the manager_id column. The president is excluded from the result set returned by the query above due to the clause that factors out the wrapper of the INNER JOIN clause.

To include the president in the result set returned, we use the left JOIN rather than the INNER JOIN clause.

SELECT 
    e.first_name || ' ' || e.last_name AS employee,
    m.first_name || ' ' || m.last_name AS manager
FROM
    employees e
        LEFT JOIN
    employees m ON m.employee_id = e.manager_id
ORDER BY manager;
Try It