Open In App

PostgreSQL – SELF JOIN

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

PostgreSQL has a special type of join called the SELF JOIN which is used to join a table with itself. It comes in handy when comparing the column of rows within the same table. As, using the same table name for comparison is not allowed in PostgreSQL, we use aliases to set different names of the same table during self-join.

It is also important to note that there is no such keyword as SELF JOIN, but it is achieved with the help of LEFT JOIN, RIGHT JOIN or INNER JOIN using aliases.

Syntax:
SELECT column_list
FROM table_name T1
INNER JOIN table_name T2 ON join_predicate;

or,

Syntax:
SELECT column_list
FROM table_name T1
LEFT JOIN table_name T2 ON join_predicate;

or,

Syntax:
SELECT column_list
FROM table_name T1
RIGHT JOIN Table_name T2 ON join_predicate;

Let’s set up a sample database and table for the demonstration of self-join.

  • Create a database named “company” with the below command:
    CREATE DATABASE company;
  • Add a table of “employee” to show the company hierarchy into the database using the below command:
    CREATE TABLE employee (
        employee_id INT PRIMARY KEY,
        first_name VARCHAR (255) NOT NULL,
        last_name VARCHAR (255) NOT NULL,
        manager_id INT,
        FOREIGN KEY (manager_id) 
        REFERENCES employee (employee_id) 
        ON DELETE CASCADE
    );
  • Now add some employee data to the table using the below command:
    INSERT INTO employee (
        employee_id,
        first_name,
        last_name,
        manager_id
    )
    VALUES
        (1, 'Sandeep', 'Jain', NULL),
        (2, 'Abhishek ', 'Kelenia', 1),
        (3, 'Harsh', 'Aggarwal', 1),
        (4, 'Raju', 'Kumar', 2),
        (5, 'Nikhil', 'Aggarwal', 2),
        (6, 'Anshul', 'Aggarwal', 2),
        (7, 'Virat', 'Kohli', 3),
        (8, 'Rohit', 'Sharma', 3);

    The value in the manager_id column represents the senior manager who the employee reports to. If it’s Null, he/she doesn’t report to anyone.
    The overall hierarchy looks like the below image:

    Now, that we have our database all set, let’s look into some examples of SELF JOIN.
    Example 1:
    Here we will query for the data of who reports to whom using the same “employee” table twice.

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


    Output:

    Example 2:
    If you remember our Sample DVD rental database used in previous articles which is explained here and can be downloaded from here, we will be performing self join in the film table of that database. Here we will query for all pairs of films that have the same runtime.

    SELECT
        f1.title,
        f2.title,
        f1. length
    FROM
        film f1
    INNER JOIN film f2 ON f1.film_id <> f2.film_id
    AND f1. length = f2. length;

    Output:


    Last Updated : 28 Aug, 2020
    Like Article
    Save Article
    Previous
    Next
    Share your thoughts in the comments
Similar Reads