PostgreSQL JOIN Types and Examples

This article introduces join statements in PostgreSQL, including cross join, inner join, natural join, left join, right join, and full join.

In PostgreSQL, the JOIN statement is used to join two or more tables.

For example, in a school system, there is a student table and a student score table. The two tables are related by the student ID column. If we want to query students’ grades, we need to join the two tables to find student information and scores.

PostgreSQL JOIN Types

PostgreSQL supports the following JOIN types:

  • cross join (CROSS JOIN)
  • inner join (INNER JOIN)
  • natural join (NATURAL JOIN)
  • left outer join (LEFT [OUTER] JOIN)
  • right outer join (RIGHT [OUTER] JOIN)
  • full outer join (RIGHT [OUTER] JOIN)

The following will give some examples for each join.

Create tables and Insert rows for Examples

The examples in this tutorial are done using two tables student and student_score.

First, use the following statements create the tables student and student_score:

CREATE TABLE student (
  student_id INTEGER NOT NULL,
  name varchar(45) NOT NULL,
  PRIMARY KEY (student_id)
);

CREATE TABLE student_score (
  student_id INTEGER NOT NULL,
  subject varchar(45) NOT NULL,
  score INTEGER NOT NULL
);

Then, insert some rows for demonstration into two tables respectively:

INSERT INTO
  student (student_id, name)
VALUES
  (1,'Tim'),(2,'Jim'),(3,'Lucy');

INSERT INTO
  student_score (student_id, subject, score)
VALUES
  (1,'English',90),
  (1,'Math',80),
  (2,'English',85),
  (5,'English',92);

Third, use the following statements to check rows in the table using SELECT statement:

SELECT * FROM student;

 student_id | name
------------+------
          1 | Tim
          2 | Jim
          3 | Lucy
(3 rows)
SELECT * FROM student_score;

 student_id | subject | score
------------+---------+-------
          1 | English |    90
          1 | Math    |    80
          2 | English |    85
          5 | English |    92
(4 rows)

Note that we’ve purposely used a special row of data for demonstration purposes:

  • The student with student_id 3 in the student table has no scores.
  • The student_id of the last row in the student_score table is 5, and there is no student with student_id 5 in the student table.

Cross Join

A cross join returns the Cartesian product of two sets. That is, all possible combinations of all rows in both tables. This is equivalent to an inner join with no join condition or the join condition being always true.

If a table with m rows and another table with n rows, their cross join will return m * n rows.

In most cases, the result of the cross join is meaningless, and you need to use the WHERE clause filter the rows you want.

The following statement cross joins student and student_score explicitly:

SELECT
  student.*,
  student_score.*
FROM
  student CROSS JOIN student_score;

The following statement cross joins student and student_score implicitly:

SELECT
  student.*,
  student_score.*
FROM
  student, student_score;

The output of both methods is the same.

 student_id | name | student_id | subject | score
------------+------+------------+---------+-------
          1 | Tim  |          1 | English |    90
          1 | Tim  |          1 | Math    |    80
          1 | Tim  |          2 | English |    85
          1 | Tim  |          5 | English |    92
          2 | Jim  |          1 | English |    90
          2 | Jim  |          1 | Math    |    80
          2 | Jim  |          2 | English |    85
          2 | Jim  |          5 | English |    92
          3 | Lucy |          1 | English |    90
          3 | Lucy |          1 | Math    |    80
          3 | Lucy |          2 | English |    85
          3 | Lucy |          5 | English |    92
(12 rows)

Inner Join

Inner joins combine rows from two tables based on join conditions. Inner joins are equivalent to cross joins with filter conditions added.

An inner join compares each row of the first table with each row of the second table and, if the given join condition is met, combines the rows of the two tables together as a row in the result set.

Venn diagram representing inner join between table A and table B
Venn diagram representing the inner join between table A and table B.

The following SQL statement joins student and student_score to find valid students and scores:

SELECT
  student.*,
  student_score.*
FROM
  student
  INNER JOIN student_score
  ON student.student_id = student_score.student_id;

Equivalent to:

SELECT
  student.*,
  student_score.*
FROM
  student, student_score
WHERE
  student.student_id = student_score.student_id;

 student_id | name | student_id | subject | score
------------+------+------------+---------+-------
          1 | Tim  |          1 | English |    90
          1 | Tim  |          1 | Math    |    80
          2 | Jim  |          2 | English |    85
(3 rows)

Note that in the output, the row student_id 3 in the student table with and the row with student_id 5 in the student_score table do not appear in the output, because they do not satisfy the join condition: student.student_id = student_score.student_id.

Since both tables use the same columns for equivalence comparison, you can use the USING clause to simplify the statement, as following:

SELECT
  student.*,
  student_score.*
FROM
  student
  INNER JOIN student_score USING(student_id);

Natural Join

Natural joins are also condition-based joins, which are a special kind of inner joins. When two tables are naturally joining, all columns with the same name in the two tables will be compared for equality. These join conditions are created implicitly.

The following SQL statement does a natural join on the tables student and student_score, and is equivalent to the inner join statement above:

SELECT
  *
FROM
  student NATURAL JOIN student_score;

 student_id | name | subject | score
------------+------+---------+-------
          1 | Tim  | English |    90
          1 | Tim  | Math    |    80
          2 | Jim  | English |    85
(3 rows)

Note that a natural join does not need ON to be created join condition, its join condition is created implicitly. In the result set of a natural join, columns with the same name in both tables appears only once.

Left Join

Left join is short for left outer join, and left join requires join conditions.

When two tables are left joined, the first table is called the left table and the second table is called the right table. For example A LEFT JOIN B, A is the left table, B is the right table.

The left join is based on the rows of the left table, and matches each row of the right table according to the join condition. If the match is successful, the rows of the left table and the right table are combined into a new row and returned; if the match is unsuccessful, the rows of the left table and NULL values ​​are combined into a new row of data to return.

Venn diagram representing left join between table A and table B
A Venn diagram representing a left join between tables A and B.

The following SQL statement is the student table left joins the student_score table:

SELECT
  student.*,
  student_score.*
FROM
  student
  LEFT JOIN student_score
  ON student.student_id = student_score.student_id;

 student_id | name | student_id | subject | score
------------+------+------------+---------+--------
          1 | Tim  |          1 | English |     90
          1 | Tim  |          1 | Math    |     80
          2 | Jim  |          2 | English |     85
          3 | Lucy |     <null> | <null>  | <null>
(4 rows)

Notice:

  1. The result set contains all the rows of the student table .
  2. The student_score table does not contain the rows with student_id 3, so the columns from the student_score table in the last rows are nulls.
  3. The student_score table has two rows with student_id 1, so there are two rows from student table with student_id 1.

Since both tables use the same columns for equivalence comparison, you can use the USING clause to simplify the statement, as following:

SELECT
  student.*,
  student_score.*
FROM
  student
  LEFT JOIN student_score USING(student_id);

Right Join

Right join is short for right outer join, right join requires join condition.

The processing logic of right join is opposite to the left join. Right join is based on the rows of the right table and matches the data in the left table according to the conditions. If the data in the left table cannot be matched, the column in the left table is the NULL value .

Venn diagram representing right join between table A and table B
A Venn diagram representing a right join between tables A and B.

The following SQL statement is the student table right joins the student_score table:

SELECT
  student.*,
  student_score.*
FROM
  student
  RIGHT JOIN student_score
  ON student.student_id = student_score.student_id;

 student_id |  name  | student_id | subject | score
------------+--------+------------+---------+-------
          1 | Tim    |          1 | English |    90
          1 | Tim    |          1 | Math    |    80
          2 | Jim    |          2 | English |    85
     <null> | <null> |          5 | English |    92
(4 rows)

As can be seen from the result set, since there is no rows with student_id 5 in the left table, the columns from the left table in the last row are nulls.

A right join is actually a left join with the left and right tables swapping positions, A RIGHT JOIN B is B LEFT JOIN A, so right joins are rarely used.

The right join in the example above can be transformed into the following left join:

SELECT
  student.*,
  student_score.*
FROM
  student_score
  LEFT JOIN student
  ON student.student_id = student_score.student_id;

Full Join

Full join is short for full outer join, which is the union of left join and right join. A full join requires a join condition.

Venn diagram representing the full join between table A and table B
A Venn diagram representing the full join between table A and table B.

The following SQL statement is the student table left joins the student_score table:

SELECT
  student.*,
  student_score.*
FROM
  student
  FULL JOIN student_score
  ON student.student_id = student_score.student_id;

 student_id |  name  | student_id | subject | score
------------+--------+------------+---------+--------
          1 | Tim    |          1 | English |     90
          1 | Tim    |          1 | Math    |     80
          2 | Jim    |          2 | English |     85
     <null> | <null> |          5 | English |     92
          3 | Lucy   |     <null> | <null>  | <null>
(5 rows)

Since both tables use the same columns for equivalence comparison, you can use the USING clause to simplify the statement, as following:

SELECT
  student.*,
  student_score.*
FROM
  student
  FULL JOIN student_score USING(student_id);

Full join is the union of left join and right join. The above full join can be rewritten using LEFT JOIN, RIGHT JOIN, and UNION:

SELECT
  student.*,
  student_score.*
FROM
  student
  LEFT JOIN student_score USING(student_id)

UNION

SELECT
  student.*,
  student_score.*
FROM
  student
  RIGHT JOIN student_score  USING(student_id);

Conclusion

This article introduced the join statements in PostgreSQL, including cross join, inner join, left join, right join, full join, and natural join.