SQL - Cross Join

---

---

The SQL Cross Join

An SQL CROSS JOIN is used to return the Cartesian product of two tables. This means every row from the first table is combined with every row from the second table, producing all possible row combinations.

A Cartesian product is the result of multiplying two sets by pairing each element of the first set with every element of the second set.

Venn Diagram of SQL CROSS JOIN

The following Venn diagram illustrates all possible combinations of rows in a SQL CROSS JOIN:

As you can see, we have two columns: Hair Style and Hair Type, each containing several records. Using a CROSS JOIN, every record in the "Hair Style" column is combined with all records in the "Hair Type" column.

The resulting table contains all possible combinations and is known as the Cartesian product or the joined table.

Syntax

Following is the basic syntax of the Cross Join query in SQL:

SELECT 
    table1.column1,
    table1.column2,
    table2.column1,
    table2.column2
FROM table1
CROSS JOIN table2;

Example

Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc., using the following query:

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows:

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 );

The table will be created as:

Let us create another table ORDERS, containing the details of orders made and the date they are made on.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Using the INSERT statement, insert values into this table as follows:

INSERT INTO ORDERS VALUES 
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00);

The table is displayed as follows:

Now, if we execute the following CROSS JOIN query on these two tables given above, the cross join combines each row in CUSTOMERS table with each row in ORDERS table.

SELECT  ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
CROSS JOIN ORDERS;

The resultant table is as follows:

Joining Multiple Tables with Cross Join

A CROSS JOIN can be used to join more than two tables, producing a Cartesian product of all the tables involved. Each row from the first table is combined with every row from the second table, and then with every row from the third table, and so on. This results in all possible combinations of rows from the joined tables.

Be cautious, because joining multiple large tables with CROSS JOIN can create extremely large result sets.

Syntax

Following is the syntax to join multiple tables using cross join in SQL:

SELECT column_name(s)
FROM table1
CROSS JOIN table2
CROSS JOIN table3
CROSS JOIN table4
....
....
....
CROSS JOIN tableN;

Example

Assume we have created another table named ORDER_RANGE using the following query:

CREATE TABLE ORDER_RANGE (
   SNO INT NOT NULL,
   ORDER_RANGE VARCHAR (20) NOT NULL
);

Now, we can insert values into this empty tables using the INSERT statement as follows:

INSERT INTO ORDER_RANGE VALUES
(1, '1-100'),
(2, '100-200'),
(3, '200-300');

The ORDER_RANGE table is created as follows:

Following query combines the three tables CUSTOMERS, ORDERS and ORDER_RANGE, using cross join:

SELECT ID, NAME, AMOUNT, DATE, ORDER_RANGE
FROM CUSTOMERS
CROSS JOIN ORDERS
CROSS JOIN ORDER_RANGE;

The resultant table is given below:

SQL CROSS JOIN with Inline Tables

SQL CROSS JOIN can also be used with inline tables or subqueries. This is useful when you want to quickly combine two sets of values without creating permanent tables.

Inline tables are temporary tables defined directly within a SQL query, often using the VALUES command or a subquery, without creating a permanent table. They allow you to quickly provide data for joins or calculations.

Example

Following is the example to create two inline tables containing some sample data and perform a CROSS JOIN on them:

SELECT A.Name, B.Product
FROM (SELECT 'Ramesh' AS Name UNION ALL SELECT 'Khilan') AS A
CROSS JOIN (SELECT 'Laptop' AS Product UNION ALL SELECT 'Mobile') AS B;

The result of this query will produce the Cartesian product of the two inline tables:

Differences Between CROSS JOIN and INNER JOIN

Both CROSS JOIN and INNER JOIN are used to combine tables, but they work very differently:

Feature	CROSS JOIN	INNER JOIN
Result	Returns the Cartesian product of two tables (all possible combinations of rows).	Returns only the rows where there is a matching value in both tables based on the join condition.
Join Condition	No join condition is required.	Requires a condition using ON or USING clause.
Use Case	Used when you want all possible combinations.	Used when you want related data from both tables.
Size of Result	Number of rows = (rows in Table1 × rows in Table2)	Number of rows ≤ min(rows in Table1, rows in Table2)

Important Points About CROSS JOIN

Following are some important points you should know for using CROSS JOIN in SQL:

• A CROSS JOIN returns the Cartesian product of the tables involved.
• It does not require a WHERE clause or a join condition.
• Be careful when using CROSS JOIN with large tables as it can produce a very large number of rows.
• CROSS JOIN can be combined with inline tables or subqueries for quick testing or data generation.
• When multiple tables are joined using CROSS JOIN, the total number of rows is the product of the number of rows in each table.