In relational databases like PostgreSQL, data can be distributed across multiple tables to ensure efficient organization. However, retrieving comprehensive information often requires fetching data from multiple tables. In that particular scenario, JOINs come in handy as they allow us to gain useful insights from the data stored in tables.
This guide will explain how to use SELF JOIN in PostgreSQL.
How to Use SELF JOIN in PostgreSQL?
SELF JOIN allows us to join/merge a table with itself and it is needed when tables are created in a hierarchy fashion, to draw the details of the table. It is more of a strategy to join a table using its own reference and joining itself, table can use either of the JOIN like INNER, LEFT, etc. It uses tables with their alias name which is the temporary name given at the time of the query execution.
Syntax With INNER JOIN
The following is the syntax for the Postgres SELF JOIN:
SELECT <Columns> FROM <Table> <Alias1> INNER JOIN <Table> <Alias2> ON Alias1.column = Alias2.column;
In this code block:
- The SELECT keyword selects one, multiple, or all columns of the table
- It starts the query at FROM keyword which contains the table name and its Alias name, which is a temporary name for the query
- An alias can be used to specify the table name and the column name of the table.
- The INNER JOIN contains the table name with an alias to join the column provided in the ON condition.
Syntax With LEFT JOIN
SELF JOIN can also be used with LEFT JOIN via the following syntax:
SELECT <Columns> FROM <Table> <Alias1> LEFT JOIN <Table> <Alias2> ON Alias1.column = Alias2.column;
The above snippet uses almost the same syntax as the previous one with a minor change, i.e. LEFT JOIN instead of INNER JOIN.
Example 1: SELF JOIN Using LEFT JOIN
To start the example, use the following query to fetch the table created on the PostgreSQL database:
SELECT * FROM orders;
The above query will return all the records of the “orders” table:
Use the following query to apply SELF JOIN on the orders table:
SELECT o1.customer_id, o1.order_date FROM orders o1 LEFT JOIN orders o2 ON o1.customer_id = o2.customer_id AND o1.order_date = o2.order_date AND o1.order_id <> o2.order_id;
The above query performs the following tasks:
- It selects two columns customer_id and order_date from the orders table and assigns them their aliases.
- After that, it applies LEFT JOIN to the order table with the second alias.
- ON condition returns the customer_id and order_date with order by order_id column:
Example 2: SELF JOIN Using INNER JOIN
Take another example using the employee table to use SELF JOIN with INNER JOIN, start by accessing the employee table using the given query:
SELECT * FROM employee;
Running the above query will return the data from the employee table:
Use the following query to apply SELF JOIN on the employee table:
SELECT a1.first_name || ' ' || a1.last_name employee, a2 .first_name || ' ' || a2 .last_name manager FROM employee a1 INNER JOIN employee a2 ON a2 .employee_id = a1.manager_id ORDER BY manager;
Here in the above code block:
- The SELECT query is used to fetch the data from the first_name and last_name columns of the employee table with their aliases assigned to them.
- INNER JOIN applies SELF JOIN on the employee table using the ON condition on the employee_id and manager_id.
- It adds to the condition using ORDER BY to display the result according to the manager hierarchy:
That’s all about using SELF JOIN in PostgreSQL.
In PostgreSQL, SELF JOIN allows the user to apply SELF JOIN to the table using JOINs like INNER, LEFT, etc. to get the reference to itself. The ON condition can be used with the INNER and LEFT JOIN to apply SELF JOIN on the table to join a table using hierarchy distribution. The tables and columns used in the SELF JOIN should contain their alias names for the query execution. This guide has explained the use of SELF JOIN in the PostgreSQL tables.