MySQL Cross Join

Last Updated: 29 June, 2023

In MySQL, a cross join, also known as a Cartesian join, is a join operation that returns the Cartesian product of two or more tables. It combines each row from the first table with every row from the second table, resulting in a potentially large result set.

The syntax for a cross join in MySQL is as follows:

 SELECT * 
 FROM table1
 CROSS JOIN table2;

Here, table1 and table2 are the names of the tables we want to cross join. The * is a wildcard that selects all columns from both tables. We can replace * with specific column names if we only want to select certain columns.

It's important to note that a cross join can produce a large result set since it generates every possible combination of rows from the joined tables. Therefore, it should be used with caution, especially when working with large tables.

Here's an example to illustrate how a cross join works:

Suppose we have two tables: Customers and Orders.

Table: Customers

  +----+----------+
  | id | name     |
  +----+----------+
  | 1  | John     |
  | 2  | Lisa     |
  +----+----------+

Table: Orders

  +----+------------+
  | id | order_date |
  +----+------------+
  | 1  | 2023-01-01 |
  | 2  | 2023-02-05 |
  | 3  | 2023-03-10 |
  +----+------------+

If we perform a cross join between these two tables, the result would be:

  +----+------+----+------------+
  | id | name | id | order_date |
  +----+------+----+------------+
  | 1  | John | 1  | 2023-01-01 |
  | 1  | John | 2  | 2023-02-05 |
  | 1  | John | 3  | 2023-03-10 |
  | 2  | Lisa | 1  | 2023-01-01 |
  | 2  | Lisa | 2  | 2023-02-05 |
  | 2  | Lisa | 3  | 2023-03-10 |
  +----+------+----+------------+

As we can see, each row from the customers table is combined with every row from the orders table, resulting in a total of six rows in the output.