Natural Join

Introduction to PostgreSQL NATURAL JOIN

A NATURAL JOIN creates an implicit join based on all columns that share the same name in both tables. You do not need to write an ON or USING clause — PostgreSQL infers the condition automatically.

Syntax:

SELECT select_list
FROM table1
NATURAL [INNER | LEFT | RIGHT] JOIN table2;

Without an explicit join type keyword, NATURAL JOIN defaults to NATURAL INNER JOIN.

Equivalent explicit forms:

-- NATURAL INNER JOIN is equivalent to:
SELECT select_list FROM table1 INNER JOIN table2 USING (shared_column);

-- NATURAL LEFT JOIN is equivalent to:
SELECT select_list FROM table1 LEFT JOIN table2 USING (shared_column);

Setting up sample tables

CREATE TABLE categories (
  category_id   SERIAL PRIMARY KEY,
  category_name VARCHAR(255) NOT NULL
);

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)
);

INSERT INTO categories (category_name)
VALUES ('Smartphone'), ('Laptop'), ('Tablet'), ('VR');

INSERT INTO products (product_name, category_id)
VALUES
  ('iPhone',          1),
  ('Samsung Galaxy',  1),
  ('HP Elite',        2),
  ('Lenovo Thinkpad', 2),
  ('iPad',            3),
  ('Kindle Fire',     3);

Basic NATURAL JOIN example

SELECT *
FROM products
NATURAL JOIN categories;

Output:

 category_id | product_id |  product_name   | category_name
-------------+------------+-----------------+---------------
           1 |          1 | iPhone          | Smartphone
           1 |          2 | Samsung Galaxy  | Smartphone
           2 |          3 | HP Elite        | Laptop
           2 |          4 | Lenovo Thinkpad | Laptop
           3 |          5 | iPad            | Tablet
           3 |          6 | Kindle Fire     | Tablet
(6 rows)

PostgreSQL joined on category_id because it is the only column name shared by both tables. The result is identical to INNER JOIN categories USING (category_id).

NATURAL LEFT JOIN to include unmatched rows

SELECT *
FROM categories
NATURAL LEFT JOIN products;

Output includes the VR category with null product values because no product belongs to category 4:

 category_id | category_name | product_id | product_name
-------------+---------------+------------+-----------------
           1 | Smartphone    |          1 | iPhone
           1 | Smartphone    |          2 | Samsung Galaxy
           2 | Laptop        |          3 | HP Elite
           2 | Laptop        |          4 | Lenovo Thinkpad
           3 | Tablet        |          5 | iPad
           3 | Tablet        |          6 | Kindle Fire
           4 | VR            |       null | null
(7 rows)

When NATURAL JOIN produces unexpected results

If both tables share an unintended column name, NATURAL JOIN silently adds it to the join condition. For example, joining city and country from the DVD rental database on both country_id and last_update returns an empty result set because the last_update values never match exactly. Use JOIN ... USING (country_id) to be explicit.

NATURAL JOIN tips

• Inspect shared column names before using NATURAL JOIN — any column name present in both tables becomes part of the implicit condition.
• Prefer JOIN ... USING (column) over NATURAL JOIN in production code: it is explicit and unaffected by future schema changes.
• Adding a column to either table that happens to share a name with the other table silently changes a NATURAL JOIN's behavior.
• Use NATURAL LEFT JOIN when you need to preserve all rows from the left table, including those with no matching row in the right table.
• If a NATURAL JOIN returns zero rows unexpectedly, check for unintended shared column names such as created_at, updated_at, or id.