SQL CROSS JOIN

SQL CROSS JOIN returns all the records from the left and right tables. CROSS JOIN returns a combination of each row in the left table paired with each row in the right table.

CROSS JOIN in SQL

Cross Join is also called a Cartesian Join as it performs cross product of records of two or more joined tables. 

Sometimes we need to match each row of one table to every other row of another table so in this case, CROSS JOIN is the best choice. Performing a cross is helpful in many applications where we need to obtain paired combinations of records.

Cross Join B/W Two Sets

Syntax of CROSS JOIN

SELECT *

FROM table1

CROSS JOIN table2;

Demo SQL Database

In this CROSS JOIN tutorial, we will use the following two tables in examples:

Table 1- Customer

ID	NAME	AGE	PHONE
1	AMIT JAIN	21	98474
2	JATIN VERMA	47	63996

Table 2- Orders

ORDER_ID	AMOUNT	PLACED_ON
101	999	2023-04-19
102	4999	2023-04-20

To create both these tables on your system, you can write the following code:

MySQL

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');    

CROSS JOIN Example

We will use the CROSS JOIN command to match the data of the Customer and Orders table.

Query

SELECT *
FROM CUSTOMER
CROSS JOIN ORDERS;

Output

Cross Join

As we can see, whether the other table matches or not, the CROSS JOIN keyword returns all similar records from both tables. Therefore, if there are rows in “Customers” or “Orders” that do not match any entries in either table, those rows will also be listed.

Key Takeaways About CROSS JOIN

• CROSS JOIN performs the cross-product of records from two or more joined tables.
• CROSS JOIN is used when we want every possible combination of rows to be present in a database’s tables. 
• When used with a WHERE clause, a cross join operates as an inner join; when used without one, it produces the cartesian product of all the rows from all the tables provided in the SQL query.

Related Links

• Self Join and Cross Join in MS SQL Server
• Difference between Inner Join and Outer Join in SQL
• Difference between Natural join and Inner Join in SQL
• Difference between Natural join and Cross join in SQL

Frequently Asked Questions About SQL CROSS JOIN

When to use the CROSS JOIN?

The CROSS JOIN in to tables used to generate all combinations of records. For example, we have two columns: size and color, and we need a result to display all the possible paired combinations of those columns that’s where the CROSS JOIN will come.

What happens when a WHERE condition is specified in CROSS JOIN?

In case a WHERE condition is specified then Cross Join behaves as a Inner Join.

What is the difference between CROSS JOIN and Natural Join?

Natural Join joins two tables based on same attribute name and datatypes. Cartesian/CROSS Join produces cartesian product of two tables.