MySQL CROSS JOIN

The CROSS JOIN in MySQL is a type of join operation that combines each row from one table with every row from another table. Unlike other join types, such as INNER JOIN or LEFT JOIN, CROSS JOIN doesn’t require any matching criteria between the tables. It simply generates the Cartesian product of the two tables involved.

Syntax

Here’s a basic syntax for a CROSS JOIN in MySQL:

SELECT * 
FROM table1
CROSS JOIN table2;

In this query, all rows from table1 will be combined with all rows from table2, resulting in a new table where each row from the first table is paired with every row from the second table.

Example

Here’s a simple example to illustrate the concept. Suppose you have two tables, employees and departments:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(255),
    emp_dep_id INT
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255)
);

INSERT INTO employees VALUES (1, 'John Doe', 1);
INSERT INTO employees VALUES (2, 'Jane Smith', 2);

INSERT INTO departments VALUES (1, 'HR');
INSERT INTO departments VALUES (2, 'IT');

Now, if you want to generate a list of all possible combinations of employees and departments, you can use a CROSS JOIN:

SELECT * 
FROM employees
CROSS JOIN departments;

The result will be:

+--------+-------------+------------+---------------+-------------------+
| emp_id | emp_name    | emp_dep_id | department_id | department_name   |
+--------+-------------+------------+---------------+-------------------+
|      1 | John Doe    |          1 |             1 | HR                |
|      1 | John Doe    |          1 |             2 | IT                |
|      2 | Jane Smith  |          2 |             1 | HR                |
|      2 | Jane Smith  |          2 |             2 | IT                |
+--------+-------------+------------+---------------+-------------------+

As you can see, each row from the employees table is combined with every row from the departments table, creating all possible combinations.

It’s important to note that while CROSS JOIN can be useful in certain situations, it can also lead to large result sets, and you should use it with caution, especially with tables containing a large number of rows.