Postgres Join

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you’ve worked with relational databases before, you may be familiar with the concept of a SQL join– combining columns from multiple tables into a set that can be used in queries. In PostgreSQL, there are several types of joins available for use, including:

  • Full outer join
  • Right join
  • Left join
  • Inner join

In this article, we’ll discuss these different forms of the Postgres join and look at some examples of their use.

Prerequisite

Before proceeding with the examples in this tutorial, make sure that PostgreSQL is installed and configured on your system.

What is a Postgres Join?

As we mentioned earlier, the Postgres join enables us to combine columns from one or more tables by locating related column values among the tables. Primary key columns of the initial table and foreign key columns of the joined table are considered the common columns.

Postgres supports the following types of joins:

  1. self-join
  2. inner-join
  3. natural-join
  4. cross-join
  5. right-join
  6. left-join
  7. full outer join

Creating Sample Dataset

In this section, we’ll create a sample dataset that we can use for the examples presented in this article.

We’ll be creating a fictitious ‘zoo’ database. There will be two tables in this database– each table will represent a cage with different animals in it.

Let’s create the tables that will hold our records:

1
2
3
4
5
6
7
8
9
CREATE TABLE cage_a (
    id INT PRIMARY KEY,
    animal VARCHAR (50)
);
 
CREATE TABLE cage_b (
    id INT PRIMARY KEY,
    animal VARCHAR (50)
);

Now we’ll insert some records:

1
2
3
4
5
6
7
8
9
10
11
12
13
 INSERT INTO cage_a (id, animal)
VALUES
    (1, 'Tiger'),
    (2, 'Lion'),
    (3, 'Eagle'),
    (4, 'Alligator');
 
INSERT INTO cage_b (id, animal)
VALUES
    (1, 'Lion'),
    (2, 'Tiger'),
    (3, 'Monkey'),
    (4, 'Bear');

Our tables will look something like the following:

CAGE_A

1
2
3
4
5
6
7
8
zoo=# select * from cage_a;
 id |  animal
----+-----------
  1 | Tiger
  2 | Lion
  3 | Eagle
  4 | Alligator
(4 rows)

CAGE_B

1
2
3
4
5
6
7
8
zoo=# select * from cage_b;
 id | animal
----+--------
  1 | Lion
  2 | Tiger
  3 | Monkey
  4 | Bear
(4 rows)

Based on the results shown above, the tables contain some animals in common, such as ‘Lion’ and ‘Tiger’.

Postgres Inner Join

In this section, we’ll look at an example of an inner join where we combine the left and right tables using the values from the ‘animal’ column:

1
2
3
4
5
6
7
8
SELECT
    a.id id_a,
    a.animal animal_a,
    b.id id_b,
    b.animal animal_b
FROM
    cage_a a
INNER JOIN cage_b b ON a.animal = b.animal;

The output should look something like the following:

1
2
3
4
5
 id_a | animal_a | id_b | animal_b
------+----------+------+----------
    1 | Tiger    |    2 | Tiger
    2 | Lion     |    1 | Lion
(2 rows)

As we can see from this result, the inner join will return a result set that contains matching values from both the left and the right tables.

The Venn diagram shown below illustrates the inner join that we performed in our example:

Postgres Right Join

In our previous example, we performed an inner join query against the tables in the ‘zoo’ database. Now, let’s look at an example of a Postgres right join.

In a query that utilizes a right join, the result set will include all the records from the right table with matching records from the left table. If there’s no match between the tables, the left side of the result will contain null values.

Let’s look at the right join query shown below:

1
2
3
4
5
6
7
8
SELECT
    a.id id_a,
    a.animal animal_a,
    b.id id_b,
    b.animal animal_b
FROM
    cage_a a
RIGHT JOIN cage_b b ON a.animal = b.animal;

The output will look something like this:

1
2
3
4
5
6
7
 id_a | animal_a | id_b | animal_b
------+----------+------+----------
    2 | Lion     |    1 | Lion
    1 | Tiger    |    2 | Tiger
      |          |    3 | Monkey
      |          |    4 | Bear
(4 rows)

The following Venn diagram is a good representation of a right join:

Postgres Left Join

Now that we know how to perform a right join, let’s try a left join query. This type of join is the opposite of a right join– we’ll be retrieving all the rows in the left table with matching records from the right table:

1
2
3
4
5
6
7
8
SELECT
    a.id id_a,
    a.animal animal_a,
    b.id id_b,
    b.animal animal_b
FROM
    cage_a a
LEFT JOIN cage_b b ON a.animal = b.animal;

The result set should look something like this:

1
2
3
4
5
6
7
 id_a | animal_a  | id_b | animal_b
------+-----------+------+----------
    1 | Tiger     |    2 | Tiger
    2 | Lion      |    1 | Lion
    3 | Eagle     |      |
    4 | Alligator |      |
(4 rows)

The left join can be viewed in the Venn diagram below:

Conclusion

When you need to combine data from multiple tables, a Postgres join is an effective way to accomplish the task. Joining tables in a PostgreSQL query gives you a combined result set containing columns from all the joined tables. In this article, we looked at the different types of Postgres joins and reviewed examples of each one. With these examples to serve as a guide, you’ll be able to implement joins in your own PostgreSQL queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.