SQL Self-Join (original) (raw)

Skip to content

Summary: in this tutorial, you’ll how to compare rows within the same table using the SQL self-join technique.

Introduction to SQL self-join #

Typically, you use a join such as inner join, left join, and right join to merge rows from two tables based on a condition.

However, a join doesn’t have to involve multiple tables. You can use a join to compare rows within the same table. In this case, you join a table to itself that forms a self-join.

A self-join is a join that compares the rows within the same table. A self-join uses an inner join, left join, or right join that joins a table to itself. It uses table aliases to treat the same table as separate tables within the same query.

Here’s the basic syntax of a self-join:

SELECT select_list FROM table1 t1 INNER JOIN table1 AS t2 ON t1.column1 = t2.column2;Code language: SQL (Structured Query Language) (sql)

In this syntax, you can use the LEFT JOIN, RIGHT JOIN, and FULL JOIN instead of the INNER JOIN.

SQL self-join example #

We’ll use the employees table from the HR sample database:

In the employees table:

• The employee_id serves as a unique identifier for each employee.
• The manager_id represents the employee_id of the manager to whom the current employee reports. If the manager_id is NULL, it means the employee is the CEO, without a manager.

The following query uses an inner join to join the employees table to itself to get the information of who reports to whom:

SELECT e.first_name employee, m.first_name manager FROM employees e LEFT JOIN employees m ON m.employee_id = e.manager_id ORDER BY manager NULLS FIRST;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

employee | manager -------------+----------- Valli | Alexander Diana | Alexander Bruce | Alexander David | Alexander Guy | Den Karen | Den Alexander | Den Shelli | Den Sigal | Den Alexander | Lex Irene | Matthew ...Code language: plaintext (plaintext)

Since the inner join clause only includes the rows with matching rows in the other table, the query does not include the CEO in the result set of the query.

To include the CEO in the result set, you can use a LEFT JOIN clause instead of the INNER JOIN clause.

Performing a self-join using a LEFT JOIN clause #

The following statement performs a self-join using a LEFT JOIN clause:

SELECT e.first_name employee, m.first_name manager FROM employees e LEFT JOIN employees m ON m.employee_id = e.manager_id ORDER BY manager NULLS FIRST;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

employee | manager -------------+----------- Steven | NULL Bruce | Alexander David | Alexander Valli | Alexander Diana | Alexander Sigal | Den Guy | Den Alexander | Den Shelli | Den Karen | Den Alexander | Lex Irene | Matthew ...Code language: plaintext (plaintext)

Summary #

• A self-join is a join that compares rows within the same tables.
• Use an inner join, left join, or full join to perform a self-join.
• Use table aliases in the query to treat the same table as two separate tables.

Quiz #

Databases #

• PostgreSQL Self Join
• Oracle Self Join
• SQL Server Self Join
• MySQL Self Join
• SQLite Self Join
• Db2 Self Join

Was this tutorial helpful ?