Open In App

PostgreSQL – Joins

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

A PostgreSQL Join statement is used to combine data or rows from one(self-join) or more tables based on a common field between them. These common fields are generally the Primary key of the first table and Foreign key of other tables.
There are 4 basic types of joins supported by PostgreSQL, namely:

  1. Inner Join
  2. Left Join
  3. Right Join
  4. Full Outer Join

Some special PostgreSQL joins are below:

  • Natural Join
  • Cross Join
  • Self Join

Let’s look into the 4 of the basic Joins in PostgreSQL.For the sake of this article, we will be setting up a sample database with the below commands in our psql shell:

  • Create a database zoo.
    CREATE DATABASE zoo;
  • Create a table zoo_1.
    CREATE TABLE zoo_1 (
        id INT PRIMARY KEY,
        animal VARCHAR (100) NOT NULL
    );
  • Create a table zoo_2.
    CREATE TABLE zoo_2 (
        id INT PRIMARY KEY,
        animal VARCHAR (100) NOT NULL
    );
  • Insert data into zoo_1 table.
    INSERT INTO zoo_1(id, animal)
    VALUES
        (1, 'Lion'),
        (2, 'Tiger'),
        (3, 'Wolf'),
        (4, 'Fox');
  • Insert data into zoo_2 table.
    INSERT INTO zoo_2(id, animal)
    VALUES
        (1, 'Tiger'),
        (2, 'Lion'),
        (3, 'Rhino'),
        (4, 'Panther');

Now, we have two tables zoo_1 and zoo_2 with two common animals and four different animals. Let’s also assume zoo_1 is the left table.

Inner Join

The below statement joins the left table with the right table using the values in the “animal” column:

SELECT
    zoo_1.id id_a,
    zoo_1.animal animal_a,
    zoo_2.id id_b,
    zoo_2.animal animal_b
FROM
    zoo_1 
INNER JOIN zoo_2 ON zoo_1.animal = zoo_2.animal;

Output:

As seen in the above output, the inner join returns a result set that contains row in the left table that matches the row in the right table.

The Venn diagram for INNER JOIN is as below:

Left Join

The below statement joins the left table with the right table using left join (or left outer join):

SELECT
    zoo_1.id,
    zoo_1.animal,
    zoo_2.id,
    zoo_2.animal
FROM
    zoo_1
LEFT JOIN zoo_2 ON zoo_1.animal = zoo_2.animal;

Output:

As seen in the output above the left join returns a complete set of rows from the left table with the matching rows if available from the right table. If there is no match, the right side will have null values.

The Venn diagram for a LEFT JOIN is as below:

Right Join

The RIGHT JOIN or RIGHT OUTER JOIN works exactly opposite to the LEFT JOIN. It returns a complete set of rows from the right table with the matching rows if available from the left table. If there is no match, the left side will have null values.

The below statement joins the right table with the left table using the right join (or right outer join):

SELECT
    zoo_1.id,
    zoo_1.animal,
    zoo_2.id,
    zoo_2.animal
FROM
    zoo_1
RIGHT JOIN zoo_2 ON zoo_1.animal = zoo_2.animal;

Output:

The Venn diagram for a RIGHT OUTER JOIN is below:

Full Outer Join

The full outer join or full join returns a result set that contains all rows from both the left and right tables, with the matching rows from both sides where available. If there is no match, the missing side contains null values.

The below statement illustrates the full outer join:

SELECT
    zoo_1.id,
    zoo_1.animal,
    zoo_2.id,
    zoo_2.animal
FROM
    zoo_1
FULL JOIN zoo_2 ON zoo_1.animal = zoo_2.animal;

Output:

The Venn diagram for a FULL OUTER JOIN is below:


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