PostgreSQL join is used to combine columns from one (self-join) or more tables based on the values of the common columns between the tables. The common columns are typically the primary key columns of the first table and foreign key columns of the second table.
PostgreSQL support inner join, left join, right join, full outer join, cross join, natural join, and a special kind of join called self-join.
Setting up sample tables
Suppose we have two tables called
basket_a and basket_b that stores fruits:
The tables have some common fruits such as apple and orange. Let’s call the
basket_a is the left table and basket_b is the right table.PostgreSQL inner join
The following statement joins the left table with the right table using the values in the
fruit column:
As you can see clearly from the result, the inner join returns a result set that contains row in the left table that match with the row in the right table.
The following Venn diagram illustrates the inner join:
PostgreSQL left join
The following statement joins the left table with the right table using left join (or left outer join):
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 following Venn diagram illustrates the left join:
To get the only rows from the left table but not in the right table, you use the left join then exclude the rows that you want from the right table using the
WHERE clause:
The output is:
The following Venn diagram illustrates the left join with only rows from the left table:
PostgreSQL right join
The right join or right outer join is a reversed version of the left join. It produces a result set that contains all rows from the right table with matching rows from the left table. If there is no match, the left side will contain null values.
The following statement performs the right join between the left and the right tables:
Here is the output:
The following Venn diagram illustrates the right join:
Similarly, you can get only rows from the right table but not from the left table by adding a
WHEREclause as follows:
The following Venn diagram illustrates the right join with only rows from the right table:
PostgreSQL full outer join
The full outer join or full join produces 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 following statement illustrates the full outer join:
Note that the
OUTER keyword is optional.
Here is the result set:
The following Venn diagram illustrates the full outer join:
To return a set of rows unique to the left and right tales, you perform the same full join first, and then exclude the rows that you don’t want from both sides using a
WHERE clause:
Here is the result:
The following Venn diagram illustrates the full outer join with only rows from both tables:
The following picture shows all the PostgreSQL joins that we discussed above with detailed syntax:
