Self Join - (PostgreSQL Join)
A self join is a regular join, but the table is joined with itself. In other words, it's a way to combine rows from a single table into a larger result set. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of a self join in PostgreSQL.
Syntax
SELECT t1.column_name1, t1.column_name2, t2.column_name1, t2.column_name2, ...
FROM table_name t1
JOIN table_name t2 ON t1.key_column = t2.key_column;
In this syntax, t1 and t2 are table aliases used to distinguish between the two copies of the same table.
Example
Let's illustrate how self join works with an example. We have a table called employees that contains the following data:
| id | name | manager_id |
|---|---|---|
| 1 | Alice | 4 |
| 2 | Bob | 4 |
| 3 | Charlie | 5 |
| 4 | Dave | 5 |
| 5 | Eve | null |
The manager_id column contains the id of the employee's manager. If an employee doesn't have a manager (i.e. they have a null value), then they are a top-level employee.
Let's say we want to retrieve a list of all employees along with their respective managers' names. We can use a self join to achieve this:
SELECT e.name, m.name as manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
The output of this query would be:
| name | manager_name |
|---|---|
| Alice | Dave |
| Bob | Dave |
| Charlie | Eve |
| Dave | Eve |
| Eve | null |
Explanation
In this example, we used a self join to join the employees table with itself. We used table aliases (e and m) to distinguish between the two copies of the table.
In the join condition, we matched the manager_id column of the employee table (e) with the id column of the manager table (m).
We selected the name column from the employee table (e) and the name column from the manager table (m) and aliased it as manager_name.
The LEFT JOIN syntax ensured that all employees, including those without managers, were included in the result set.
Use
Self join is useful when you have a table with a hierarchical structure, such as a table containing employees and their managers. By joining the table with itself based on the hierarchical relationship, you can retrieve information about each employee and their respective managers.
Important Points
- To avoid ambiguity, it's important to use table aliases when performing a self join.
- When joining a table with itself, make sure that the join condition matches the appropriate columns in each copy of the table.
- Use an appropriate join type (e.g.
LEFT JOIN) to include all relevant rows from the table.
Summary
In this tutorial, we discussed the syntax, example, output, explanation, use, and important points of a self join in PostgreSQL. We showed you how to use a self join to retrieve information about employees and their managers and discussed other use cases for self join.