PostgreSQL Cross Join

Summary: in this tutorial, you’ll learn how to use the PostgreSQL CROSS JOIN to merge rows from two tables.

Exploring PostgreSQL Cross Join clause #

The CROSS JOIN combines each row from the first table with every row from the second table and returns combinations of all rows.  

Unlike other joins like inner join, left join, and full join, a cross join has no condition to match rows from the two tables.

Here’s the syntax of the CROSS JOIN clause:

SELECT
  table1.column1,
  table2.column2,
  ...
FROM
  table1
  CROSS JOIN table2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the name of the first table (table1) in the FROM clause.
• Second, provide the name of the second table (table2) in the CROSS JOIN clause.
• Third, list the columns from both tables that you want to retrieve data in the SELECT clause.

If table1 has n rows and table2 has m rows, the CROSS JOIN will return a result set that has nxm rows.

Alternatively, you can form a cross-join by listing two tables in the FROM clause, making the statement more concise but less obvious:

SELECT
  table1.column1,
  table2.column2,
  ...
FROM
  table1, table2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In practice, you use a cross-join when you want to have all possible combinations of rows from both tables.

Understanding the cross join #

Suppose you want to join two tables X and Y using a cross join:

• The X table has two columns id (key) and x.
• The Y table also has two columns id (key) and y.

The cross-join returns a result set that includes all possible combinations of rows from the left table with rows from the right table:

The following diagram is another way to depict a cross join:

Setting up sample tables #

First, create a new table called products:

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

Second, insert three rows into the products table:

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

Output:

 product_id |       name       |  price
------------+------------------+---------
          1 | iPhone 14 Pro    |  999.99
          2 | iPhone 15 Pro    | 1199.99
          3 | Galaxy S23 Ultra | 1149.47Code language: plaintext (plaintext)

Third, create a new table called warehouses:

CREATE TABLE warehouses (
  warehouse_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, insert two rows into the warehouses table:

INSERT INTO
  warehouses(name)
VALUES
  ('San Jose'),
  ('San Francisco')
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 warehouse_id |     name
--------------+---------------
            1 | San Jose
            2 | San FranciscoCode language: plaintext (plaintext)

PostgreSQL CROSS JOIN example #

Suppose you must go to each warehouse and perform a physical inventory check. In this process, you must check the warehouse name, product, and quantity.

You can produce a list that includes all possible combinations of warehouses and products before checking the inventory using a cross-join:

SELECT
  w.name warehouse_name,
  p.name product_name,
  '' quantity 
FROM
  products p
  CROSS JOIN warehouses w;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_name |   product_name   | quantity
----------------+------------------+----------
 San Jose       | iPhone 14 Pro    |
 San Jose       | iPhone 15 Pro    |
 San Jose       | Galaxy S23 Ultra |
 San Francisco  | iPhone 14 Pro    |
 San Francisco  | iPhone 15 Pro    |
 San Francisco  | Galaxy S23 Ultra |Code language: plaintext (plaintext)

The third column quantity is a blank for filling out the quantity of each product per warehouse.

The following statement uses the alternative syntax of the cross-join to merge the rows from products and warehouses tables:

SELECT
  w.name warehouse_name,
  p.name product_name,
  '' quantity 
FROM
  products p, warehouses w;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

It returns the same output as the query above.

Summary #

• Use the CROSS JOIN to return all combinations of rows from two tables.

Quiz #