MySQL : CROSS JOIN Keyword

In MySQL, the CROSS JOIN keyword is used to produce a Cartesian product of two tables. It combines each row from the first table with each row from the second table, resulting in a combination of all possible pairs of rows.

Here's the basic syntax of the CROSS JOIN:


SELECT columns
FROM table1
CROSS JOIN table2;
 

  • columns: The columns you want to retrieve from the Cartesian product of the two tables.
  • table1, table2: The names of the tables you want to combine.

Example:

Let's say we have two tables: employees and departments. The employees table contains information about employees, and the departments table contains information about departments.

If we use a CROSS JOIN between these two tables, it will generate a result set where each employee is paired with each department, resulting in all possible combinations.


SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
CROSS JOIN departments;
 

In this query:

  • Each row from the employees table is combined with each row from the departments table.
  • If there are m rows in the employees table and n rows in the departments table, the result will contain m * n rows.
  • There is no explicit join condition specified with an ON clause because a CROSS JOIN generates all possible combinations.

CROSS JOIN can be useful in some scenarios, such as generating all possible combinations for analysis or creating temporary tables for data manipulation. However, it can also produce a very large result set if used with large tables, so it should be used with caution.