SQL CROSS JOIN

CROSS JOIN in SQL generates the Cartesian product of two tables, meaning each row from the first table is paired with every row from the second. This is useful when you want all possible combinations of records. Since result grows as rows_in_table1 × rows_in_table2, it can get very large, so it’s best used with smaller tables or alongside a WHERE clause to filter results into meaningful pairs.

Syntax:

SELECT * FROM table1
CROSS JOIN table2;

Examples of SQL CROSS JOIN

Before diving into queries, let’s create two sample tables: Customer and Orders. These tables will help us understand how CROSS JOIN combines data into multiple combinations.

Table 1- Customer

CREATE TABLE Customer (
   ID INT,
   NAME VARCHAR(50),
   AGE INT,
   PHONE VARCHAR(10) );

INSERT INTO Customer (ID, NAME, AGE, PHONE) VALUES
(1, 'AMIT JAIN', 21, 98474),
(2, 'JATIN VERMA', 47, 63996);

Table 2- Orders

CREATE TABLE Orders (
   ORDER_ID INT,
   AMOUNT INT,
   PLACED_ON DATE );

INSERT INTO Orders (ORDER_ID, AMOUNT, PLACED_ON) VALUES
(101, 999, '2023-04-19'),
(102, 4999, '2023-04-20');

Now, let’s perform a CROSS JOIN to see how every Customer pairs with every Order In the below example, we will use the CROSS JOIN command to match the data of the Customer and Orders table.

In general, if table A has m rows and table B has n rows, CROSS JOIN result will have m × n rows.

Query:

SELECT * 
FROM Customer
CROSS JOIN Orders;

Output:

Explanation: CROSS JOIN combines every row from the Customer table with every row from Orders table. Since there are 2 customers and 2 orders, result contains 2 × 2 = 4 rows.