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 in SQL produces a result set that contains the cartesian product of two or more tables. Cross join is also called a Cartesian Join.
When CROSS JOIN is used with a WHERE clause, it behaves like INNER JOIN, filtering the results based on specific conditions.
CROSS JOIN is the best choice when we need to match each row of one table to every other row of another table. It is helpful in many applications where we need to obtain paired combinations of records.
Cross Join B/W Two Sets
Syntax
SELECT * FROM table1
CROSS JOIN table2;
SQL CROSS JOIN Example
Let’s look at some examples of CROSS JOIN statement in SQL to understand it’s working.
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:
CREATE DATABASE w3wiki;
USE w3wiki;
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
In this 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.
Important Points About CROSS JOIN
- CROSS JOIN performs the cross-product of records from two or more joined tables.
- It is used when we want every possible combination of rows to be present in a database’s tables.
- SQL CROSS JOIN with condition of WHERE Clause 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.
- CROSS JOIN is different from other join types like INNER JOIN, LEFT JOIN, and RIGHT JOIN, as it does not require a matching condition between the tables.
Related Links
Frequently Asked Questions on SQL CROSS JOIN
When to use the CROSS JOIN in SQL?
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.