SQL SELF JOIN
Summary: This tutorial shows you how to join the table itself by using the SQL self-join technique.
Introduction to SQL self-join
Sometimes, it is useful to join a table to itself. This type of join is known as self-join.
We join a table to itself to evaluate the rows with other rows in the same table. To perform the self-join, we use either an inner join or left join clause.
Because the same table appears twice in a single query, we have to use the table aliases. The following statement illustrates how to join a table to itself.
SELECT
column1,
column2,
column3,
...
FROM
table1 A
INNER JOIN table1 B ON B.column1 = A.column2;
This statement joins the table1 to itself using an INNER JOIN clause. A and B are the table aliases of the table1. The B.column1 = A.column2
is the join condition.
Besides the INNER JOIN clause, you can use the LEFT JOIN clause.
Let’s take a few examples of using the self-join technique.
SQL self-join examples
See the following employees
table.
The manager_id
column specifies the manager of an employee. The following statement joins the employee's table to itself to query the information of who reports to whom.
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;
The president does not have any managers. In the employee's table, the manager_id of the row that contains the president is NULL.
Because the inner join clause only includes the rows that have matching rows in the other table, therefore the president did not show up in the result set of the query above.
To include the president in the result set, we use the LEFT JOIN clause instead of the INNER JOIN clause like the following query.
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;
In this tutorial, you have learned how to use the INNER JOIN or LEFT JOIN clause to join the table to itself.
0 Comments
CAN FEEDBACK
Emoji