What is SQL CROSS JOIN?

The SQL CROSS JOIN, also known as Cartesian Join, returns the Cartesian product of two tables. This means it combines each row from the first table with every row from the second table, creating a result set with all possible pairs of rows.

Syntax

SELECT *
FROM table1
CROSS JOIN table2;

Examples

Database Setup:

Create and populate two tables, CUSTOMER and ORDERS:

CREATE DATABASE GeeksForGeeks;
USE GeeksForGeeks;

CREATE TABLE CUSTOMER(
    ID INT,
    NAME VARCHAR(20),
    AGE INT,
    PHONE INT
);

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

INSERT INTO CUSTOMER VALUES (1, 'AMIT JAIN', 21, 98474);
INSERT INTO CUSTOMER VALUES (2, 'JATIN VERMA', 47, 63996);

INSERT INTO ORDERS VALUES (101, 999, '2023-04-19');
INSERT INTO ORDERS VALUES (102, 4999, '2023-04-20');

Example Query:

Combine data from CUSTOMER and ORDERS:

SELECT *
FROM CUSTOMER
CROSS JOIN ORDERS;

Output:

The result will include all possible combinations of rows from CUSTOMER and ORDERS.

Important Points

• Cartesian Product: CROSS JOIN generates the Cartesian product of the tables involved.
• No Matching Condition: Unlike INNER JOIN, CROSS JOIN does not require a matching condition between the tables.
• With WHERE Clause: If a WHERE clause is used, CROSS JOIN behaves like an INNER JOIN, filtering the results based on specified conditions.
• Use Case: Useful for generating all possible combinations of rows, which is helpful in scenarios like creating paired combinations of records.

Frequently Asked Questions

• When to use CROSS JOIN? Use it to generate all possible combinations of rows between two tables, such as pairing sizes with colors.
• Effect of WHERE Clause: If used, CROSS JOIN with a WHERE clause behaves like an INNER JOIN, filtering results based on the condition.
• Difference from Natural Join: A CROSS JOIN produces a Cartesian product, whereas a Natural Join combines rows based on matching attribute names and data types.

Let me know if you need further details or have any other questions!