A natural join is a join that creates an implicit join based on the same column names in the joined tables. See the following syntax of PostgreSQL natural join:
A natural join can be an inner join, left join, or right join. If you do not specify a join explicitly e.g.,
INNER JOIN, LEFT JOIN, RIGHT JOIN, PostgreSQL will use the INNER JOIN by default.
If you use the asterisk (*) in the select list, the result will contain the following columns:
- All the common columns, which are the columns in the both tables that have the same name
- Every column in the first and second tables that is not a common column
PostgreSQL NATURAL JOIN examples
To demonstrate the PostgreSQL natural join, we will create two tables:
categories and products. The following CREATE TABLE statements create the categories and products table.
Each category has zero or many products whereas each product belongs to one and only one category. The
category_id column in the products table is the foreign key that references to the primary key of the categories table. The category_id is the common column that we will use to perform the natural join.
The following statement uses the
NATURAL JOIN clause to join the products table with the categories table:
The above statement is equivalent to the following statement that uses the
INNER JOIN clause.
The convenience of the
NATURAL JOIN is that it does not require you to specify the join clause because it uses an implicit join clause based on the common column.
However, you should avoid using the
NATURAL JOIN whenever possible because sometimes it may cause an unexpected result.
For example, let’s take a look at the
city and country tables. Both tables have the same country_id column so we can use the NATURAL JOIN to join these tables as follows: