PostgreSQL Natural Join

Summary: in this tutorial, you’ll learn how to use PostgreSQL NATURAL JOIN to join tables based on columns with the same names.

An Insight Into PostgreSQL NATURAL JOIN #

In PostgreSQL, a NATURAL JOIN allows you to join tables based on columns with the same names in both tables.

Here’s the syntax of the NATURAL JOIN:

SELECT select_list
FROM table1
NATURAL [INNER, LEFT, RIGHT, FULL] JOIN table2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

The NATURAL JOIN joins two tables, table1and table2. These tables need the same column name and compatible types for matching.

The NATURAL JOIN can be INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

If you don’t specify a join after the NATURAL keyword, PostgreSQL will use an INNER JOIN by default. If table1 and table2 have no common column, the NATURAL JOIN behaves like a CROSS JOIN.

Setting up sample tables #

First, create a new table called brands:

CREATE TABLE brands (
  brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  brand_name VARCHAR(255) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, insert rows into the brands table:

INSERT INTO
  brands (brand_name)
VALUES
  ('Apple'),
  ('Samsung'),
  ('Google') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 brand_id | brand_name
----------+------------
        1 | Apple
        2 | Samsung
        3 | GoogleCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, create a new table called products:

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  brand_id INT,
  FOREIGN KEY (brand_id) REFERENCES brands (brand_id)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, insert rows into the products table:

INSERT INTO
  products (product_name, price, brand_id)
VALUES
  ('iPhone 14 Pro', 999.99, 1),
  ('iPhone 15 Pro', 1199.99, 1),
  ('Galaxy S23 Ultra', 1149.47, 2),
  ('Oppo Find Flip', 499.99, NULL) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 product_id |   product_name   |  price  | brand_id
------------+------------------+---------+----------
          1 | iPhone 14 Pro    |  999.99 |        1
          2 | iPhone 15 Pro    | 1199.99 |        1
          3 | Galaxy S23 Ultra | 1149.47 |        2
          4 | Oppo Find Flip   |  499.99 |     NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL NATURAL JOIN examples #

We’ll use NATURAL JOIN to join the brands and products tables.

INNER JOIN #

The following statement uses a NATURAL JOIN to perform an INNER JOIN between the products and brands tables:

SELECT
  product_name,
  price,
  brand_name
FROM
  products
  NATURAL INNER JOIN brands;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name   |  price  | brand_name
------------------+---------+------------
 iPhone 14 Pro    |  999.99 | Apple
 iPhone 15 Pro    | 1199.99 | Apple
 Galaxy S23 Ultra | 1149.47 | SamsungCode language: plaintext (plaintext)

LEFT JOIN #

The following example uses a NATURAL JOIN to join the products and brands tables using a LEFT JOIN:

SELECT
  product_name,
  price,
  brand_name
FROM
  products
  NATURAL LEFT JOIN brands;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name   |  price  | brand_name
------------------+---------+------------
 iPhone 14 Pro    |  999.99 | Apple
 iPhone 15 Pro    | 1199.99 | Apple
 Galaxy S23 Ultra | 1149.47 | Samsung
 Oppo Find Flip   |  499.99 | NULLCode language: plaintext (plaintext)

RIGHT JOIN #

The following statement uses a NATURAL JOIN to join the products and brands tables using a RIGHT JOIN:

SELECT
  product_name,
  price,
  brand_name
FROM
  products
  NATURAL RIGHT JOIN brands;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name   |  price  | brand_name
------------------+---------+------------
 iPhone 14 Pro    |  999.99 | Apple
 iPhone 15 Pro    | 1199.99 | Apple
 Galaxy S23 Ultra | 1149.47 | Samsung
 NULL             |    NULL | GoogleCode language: plaintext (plaintext)

FULL JOIN #

The following example uses a NATURAL JOIN to join the products and brands tables using a FULL JOIN:

SELECT
  product_name,
  price,
  brand_name
FROM
  products
  NATURAL FULL JOIN brands;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name   |  price  | brand_name
------------------+---------+------------
 iPhone 14 Pro    |  999.99 | Apple
 iPhone 15 Pro    | 1199.99 | Apple
 Galaxy S23 Ultra | 1149.47 | Samsung
 Oppo Find Flip   |  499.99 | NULL
 NULL             |    NULL | GoogleCode language: plaintext (plaintext)

PostgreSQL NATURAL JOIN caveats #

PostgreSQL NATURAL JOIN relies on the same column names for matching rows. If you change the table structure, the query may work differently than expected. For example:

DROP TABLE products, brands;

CREATE TABLE brands (
  brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  brand_id INT,
  FOREIGN KEY (brand_id) REFERENCES brands (brand_id)
);

INSERT INTO
  brands (name)
VALUES
  ('Apple'),
  ('Samsung'),
  ('Google') 
RETURNING *;

INSERT INTO
  products (name, price, brand_id)
VALUES
  ('iPhone 14 Pro', 999.99, 1),
  ('iPhone 15 Pro', 1199.99, 1),
  ('Galaxy S23 Ultra', 1149.47, 2),
  ('Oppo Find Flip', 499.99, NULL) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

We expect the following query to use the brand_id column for joining the two tables:

SELECT
  p.name product_name,
  p.price,
  b.name brand_name
FROM
  products p
  NATURAL INNER JOIN brands b;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

However, it returns an empty set:

 product_name | price | brand_name
--------------+-------+------------Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The reason is that the products and brands tables have the same brand_id and name columns.

In this case, the NATURAL JOIN uses the values from both brand_id and name columns for matching rows. The NATURAL JOIN behaves like the following query:

SELECT
  p.name product_name,
  p.price,
  b.name brand_name
FROM
  products p
  INNER JOIN brands b ON b.brand_id = p.brand_id
  AND b.name = p.name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Since product names differ from brand names, the join condition is false, which causes the query to return an empty set.

Summary #

PostgreSQL NATURAL JOIN uses common column names to join two tables based on common columns.
Use NATURAL JOIN with caution.

Quiz #

Was this tutorial helpful ?