PostgreSQL – Joins

The PostgreSQL JOIN statement is a powerful tool for combining data or rows from one or more tables based on a common field between them. These common fields are typically the primary key in the first table and the foreign key in the other table(s). By using different types of JOINs, we can perform complex data retrieval operations and manage relationships between tables efficiently.

In this article, we will explain the different types of JOINs in PostgreSQL, explain their usage, and provide examples with outputs for better understanding.

Types of JOINS

There are 4 basic types of joins supported by PostgreSQL. Each type of JOIN serves a unique purpose and is useful in specific situations, which we will explore in detail.

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN

In addition, PostgreSQL supports special JOINs:

• NATURAL JOIN
• CROSS JOIN
• SELF JOIN

Setting Up Sample Tables

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

Query:

-- Create database
CREATE DATABASE zoo;

-- Create table zoo_1
CREATE TABLE zoo_1 (
    id INT PRIMARY KEY,
    animal VARCHAR(100) NOT NULL
);

-- Create table zoo_2
CREATE TABLE zoo_2 (
    id INT PRIMARY KEY,
    animal VARCHAR(100) NOT NULL
);

-- Insert data into zoo_1
INSERT INTO zoo_1 (id, animal)
VALUES
    (1, 'Lion'),
    (2, 'Tiger'),
    (3, 'Wolf'),
    (4, 'Fox');

-- Insert data into zoo_2
INSERT INTO zoo_2 (id, animal)
VALUES
    (1, 'Tiger'),
    (2, 'Lion'),
    (3, 'Rhino'),
    (4, 'Panther');

Output

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 and ‘zoo_2′ is the right table.

Table zoo_1:

Table zoo_2:

These tables are now ready to use for join operations based on the animal column in each table

PostgreSQL INNER JOIN

An INNER JOIN returns rows with matching values in both tables. This join is useful when we want only records that exist in both tables. The below statement joins the left table with the right table using the values in the “animal” column.

Query:

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

PostgreSQL Inner Join

Explanation:

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 INNER JOIN compares zoo_1 with zoo_2 based on the animal column and returns only those rows where there is a match in both tables (Lion and Tiger).

The Venn diagram for INNER JOIN is as below:

PostgreSQL LEFT JOIN

A LEFT JOIN returns all records from the left table (zoo_1) and matched records from the right table (zoo_2). If there’s no match, the columns from the right table contain NULL values. The below statement joins the left table with the right table using LEFT JOIN.

Query:

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

PostgreSQL Left Join

Explanation:

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:

PostgreSQL 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).

Query:

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

PostgreSQL Right Join

The Venn diagram for a RIGHT OUTER JOIN is below:

PostgreSQL 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.

Query:

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

PostgreSQL Full Outer Join

The Venn diagram for a FULL OUTER JOIN is below:

Full Outer Join Venn Diagram

Explanation:

The FULL OUTER JOIN includes all rows from both tables. Unmatched rows from each side are filled with NULL values.

Conclusion

PostgreSQL JOINs offer powerful ways to combine tables, whether using FULL OUTER JOIN to include all records or more specific joins to retrieve only matched data. Understanding JOIN syntax and selecting the right type of JOIN for our queries can greatly enhance data organization and analysis. With these techniques, combining tables in PostgreSQL becomes efficient, enabling complex relational data handling across multiple tables.

FAQs

What are JOINs in Postgres?

JOINs in PostgreSQL are used to combine rows from two or more tables based on a related column, allowing for complex data retrieval across multiple tables. Common columns, like primary and foreign keys, are typically used to define the relationships.

How many JOINs are in PostgreSQL?

PostgreSQL supports several types of JOINs: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, NATURAL JOIN, and SELF JOIN.

How do I join two queries in Postgres?

To join results from two queries in PostgreSQL, use JOIN clauses in a single SELECT statement with the ON keyword to specify the join condition.

R

RajuKumar19

Follow

Improve

Article Tags :

• Databases
• PostgreSQL
• Python
• postgreSQL-joins

Practice Tags :

• python