August 3, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL
NATURAL JOIN to query data from two or more tables.
A natural join is a join that creates an implicit join based on the same column names in the joined tables.
The following shows the syntax of the PostgreSQL natural join:
SELECT select_list FROM T1 NATURAL [INNER, LEFT, RIGHT] JOIN T2;
If you use the asterisk (
*) in the select list, the result will contain the following columns:
- All the common columns, which are the columns from both tables that have the same name.
- Every column from both tables, which is not a common column.
PostgreSQL NATURAL JOIN examples
To demonstrate the PostgreSQL natural join, we will create two tables:
CREATE TABLE statements create the
DROP TABLE IF EXISTS categories; CREATE TABLE categories ( category_id serial PRIMARY KEY, category_name VARCHAR (255) NOT NULL ); DROP TABLE IF EXISTS products; CREATE TABLE products ( product_id serial PRIMARY KEY, product_name VARCHAR (255) NOT NULL, category_id INT NOT NULL, FOREIGN KEY (category_id) REFERENCES categories (category_id) );
Each category has zero or many products and each product belongs to one and only one category.
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 INSERT statements insert some data into the
INSERT INTO categories (category_name) VALUES ('Smart Phone'), ('Laptop'), ('Tablet'); INSERT INTO products (product_name, category_id) VALUES ('iPhone', 1), ('Samsung Galaxy', 1), ('HP Elite', 2), ('Lenovo Thinkpad', 2), ('iPad', 3), ('Kindle Fire', 3);
The following statement uses the
NATURAL JOIN clause to join the
products table with the
SELECT * FROM products NATURAL JOIN categories;
The above statement is equivalent to the following statement that uses the
INNER JOIN clause.
SELECT * FROM products INNER JOIN categories USING (category_id);
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, See the following
country tables from the sample database:
Both tables have the same
country_id column so you can use the
NATURAL JOIN to join these tables as follows:
SELECT * FROM city NATURAL JOIN country;
The query returns an empty result set.
The reason is that…
Both tables also have another common column called
last_update, which cannot be used for the join. However, the
NATURAL JOIN clause just uses the
In this tutorial, you have learned about the PostgreSQL
NATURAL JOIN works and how to use it to query data from two or more tables that have the common columns.