In SQL, the CROSS JOIN
operation allows us to combine rows from two or more tables without any specific relationship between them.
Example
SELECT *
FROM Customers
CROSS JOIN Orders;
Here, the SQL query combines each row of the Customers table with each row of the Orders table.
CROSS JOIN Syntax
The syntax of the SQL CROSS JOIN
operation is:
SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;
Here,
column1
andcolumn2
- the table columnstable1
andtable2
- the names of the tables we want to combine
Example: SQL CROSS JOIN
SELECT Customers.customer_id, Customers.first_name, Orders.order_id
FROM Customers
CROSS JOIN Orders;
Here, the SQL command performs a CROSS JOIN
operation between the Customers and Orders tables.
This creates a Cartesian product of all customer IDs and first names with order IDs.
The result is a combination of every customer with every order.
CROSS JOIN With Multiple Tables
We can also perform CROSS JOIN
with more than two tables. For example,
SELECT Customers.customer_id, Orders.item, Shippings.status
FROM Customers
CROSS JOIN Orders
CROSS JOIN Shippings;
Here, the SQL command combines rows from the Customers, Orders, and Shippings tables to create a Cartesian product.
SQL CROSS JOIN With Aliases
We can use aliases with table names to make our snippet short and clean. For example,
SELECT c.customer_id, o.item, s.status
FROM Customers c
CROSS JOIN Orders o
CROSS JOIN Shippings s;
In this example, c
, o
, and s
are aliases for the Customers, Orders, and Shippings tables, respectively.
These aliases make the query more concise and readable.