Description

SQL CROSS JOIN can be used to join two tables, that combine each row from the first table with each row from the second table.

  • This join generates a Cartesian product or multiplication of the rows from both tables, where each row on the first table is repeated as many times as the rows on the second table.
  • The number of rows in the CROSS JOIN is the product of the number of rows in each table.
  • It doesn't need a condition to join the tables, so it is less useful and rarely used than the other joins.

In the case of common columns between the two tables, the column is included only once in the result set and its value is pulled from the second table.

Syntax

Here is the basic syntax of a SELECT statement with a CROSS JOIN.

  • The table1 and table2 represents the first and second table names.
  • The column_list represents the column names from either or both the tables.
SELECT column_list
FROM table1
CROSS JOIN table2;

Table Data

Consider the below data in the orders, customers, and shippers tables, with the below observations.

  • Tables orders and customers are relationally connected via customer_id column.
  • Tables orders and shippers are relationally connected via shipper_id column.

Orders Table Data

order_id order_date order_value customer_id shipper_id
10250 2014-07-05 1807.5 3 1
10251 2014-07-15 1159 2 2
10252 2014-07-18 2822 1 3
10253 2014-07-19 2575.3 3 2
10254 2014-07-24 1256.25 1 3
10255 2014-07-31 9247.5 5 1

Customers Table Data

customer_id customer_name address  city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
3 Dominique Perrier 25, rue Lauriston Paris France 75016

Shipper Table Data

shipper_id shipper_name phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931

Cross Join

The below SQL statement fetches data from both tables by joining them together as mentioned below.

  • Returns a Cartesian product or multiplication of the rows, by combining each row from the first table with each row from the second table.
  • Each row on the first table is repeated as many times as the rows on the second table.

Run this on IDE

SELECT order_id, order_date, order_value, customer_name
FROM orders
CROSS JOIN customers;

After successful execution, the output contains a Cartesian product of table rows, as shown below.

order_id order_date order_value customer_name
10250 2014-07-05 1807.5 Maria Anders
10250 2014-07-05 1807.5 Fran Wilson
10250 2014-07-05 1807.5 Dominique Perrier
10251 2014-07-15 1159 Maria Anders
10251 2014-07-15 1159 Fran Wilson
10251 2014-07-15 1159 Dominique Perrier
10252 2014-07-18 2822 Maria Anders
10252 2014-07-18 2822 Fran Wilson
10252 2014-07-18 2822 Dominique Perrier
10253 2014-07-19 2575.3 Maria Anders
10253 2014-07-19 2575.3 Fran Wilson
10253 2014-07-19 2575.3 Dominique Perrier
10254 2014-07-24 1256.25 Maria Anders
10254 2014-07-24 1256.25 Fran Wilson
10254 2014-07-24 1256.25 Dominique Perrier
10255 2014-07-31 9247.5 Maria Anders
10255 2014-07-31 9247.5 Fran Wilson
10255 2014-07-31 9247.5 Dominique Perrier

Cross Join All Columns

The below SQL statement fetches data from both the tables as mentioned below.

  • The SELECT * returns all the columns from both the tables, without repeating the common columns.
  • For the common columns, the value is pulled from the second table.

Run this on IDE

SELECT *
FROM orders
CROSS JOIN customers;

After successful execution, the output contains data from both the tables as shown below.

order_id order_date order_value customer_id supplier_id customer_name address city country postal_code
10250 2014-07-05 1807.5 1 1 Maria Anders Obere Str. 57 Berlin Germany 12209
10250 2014-07-05 1807.5 2 1 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
10250 2014-07-05 1807.5 3 1 Dominique Perrier 25, rue Lauriston Paris France 75016
10251 2014-07-15 1159 1 2 Maria Anders Obere Str. 57 Berlin Germany 12209
10251 2014-07-15 1159 2 2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
10251 2014-07-15 1159 3 2 Dominique Perrier 25, rue Lauriston Paris France 75016
10252 2014-07-18 2822 1 3 Maria Anders Obere Str. 57 Berlin Germany 12209
10252 2014-07-18 2822 2 3 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
10252 2014-07-18 2822 3 3 Dominique Perrier 25, rue Lauriston Paris France 75016
10253 2014-07-19 2575.3 1 2 Maria Anders Obere Str. 57 Berlin Germany 12209
10253 2014-07-19 2575.3 2 2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
10253 2014-07-19 2575.3 3 2 Dominique Perrier 25, rue Lauriston Paris France 75016
10254 2014-07-24 1256.25 1 3 Maria Anders Obere Str. 57 Berlin Germany 12209
10254 2014-07-24 1256.25 2 3 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
10254 2014-07-24 1256.25 3 3 Dominique Perrier 25, rue Lauriston Paris France 75016
10255 2014-07-31 9247.5 1 1 Maria Anders Obere Str. 57 Berlin Germany 12209
10255 2014-07-31 9247.5 2 1 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
10255 2014-07-31 9247.5 3 1 Dominique Perrier 25, rue Lauriston Paris France 75016

Tips & Recommendations

A CROSS JOIN generates a Cartesian product or multiplication of the rows from both tables, where each row on the first table is repeated as many times as the rows on the second table.

So, the number of rows in the CROSS JOIN result set is a product of the number of rows in each table.

Overall

We now know how can we use a SELECT Statement with CROSS JOIN to generate a Cartesian product of the two joined tables.

Related Links