PostgreSQL – Joins

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:

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.