PostgreSQL – SELF JOIN

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: