SQL CROSS JOIN

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 and column2 - the table columns
  • table1 and table2 - 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.

Did you find this article helpful?