SQL CROSS JOIN

The CROSS JOIN is used to combine each row from the first table with each row from the second table.

The CROSS JOIN returns a result set, which is the number of rows in the first table (table A) multiplied by the number of rows in the second table (table B).

A CROSS JOIN is also called a CARTESIAN JOIN.

When a WHERE clause is used with a CROSS JOIN, it will be like an INNER JOIN.

The following illustration shows how cross join works:

Syntax

The syntax for the CROSS JOIN is as follows:

SELECT column_list
FROM tableA
CROSS JOIN tableB

or

SELECT column_list 
FROM tableA, tableB

Demo table

Let us suppose that we have the following "Employee" and "Department" tables:

• Table: Employee:

  +--------+-------------+-----------+-----------+---------+
  | emp_no | first_name  | last_name | salary    | dept_id |
  +--------+-------------+-----------+-----------+---------+
  | 1001   | James       | Smith     | 6600      | 1       |
  | 1002   | Maria       | Martinez  | 9000      | 2       |
  | 1003   | Andrew      | Rodriguez | 9500      | 3       |
  | 1004   | Donna       | Williams  | 7400      | NULL    |
  +--------+-------------+-----------+-----------+---------+
  

• Table : Department:

  +---------+------------------+
  | dept_id | dept_name        |
  +---------+------------------+
  | 1       | Customer Service |
  | 2       | Development      |
  | 3       | Finance          |
  | 4       | Human Resources  |
  +---------+------------------+
  

SQL CROSS JOIN Example

When you use a CROSS JOIN, you will create a cartesian product, or a multiplication of all rows in the first table will all rows in the second tables.

The following SQL statement will do a CROSS JOIN between the "Employee" and "Department" tables.

SELECT em.emp_no, em.first_name, em.last_name, dep.dept_name
FROM employee AS em
CROSS JOIN department AS dep;

After running the above SQL statement, the result set will look as follows:

+--------+-------------+-----------+------------------+
| emp_no | first_name  | last_name | dept_name        |
+--------+-------------+-----------+------------------+
| 1001   | James       | Smith     | Customer Service |
| 1002   | Maria       | Martinez  | Customer Service |
| 1003   | Andrew      | Rodriguez | Customer Service |
| 1004   | Donna       | Williams  | Customer Service |
| 1001   | James       | Smith     | Development      |
| 1002   | Maria       | Martinez  | Development      |
| 1003   | Andrew      | Rodriguez | Development      |
| 1004   | Donna       | Williams  | Development      |
| 1001   | James       | Smith     | Finance          |
| 1002   | Maria       | Martinez  | Finance          |
| 1003   | Andrew      | Rodriguez | Finance          |
| 1004   | Donna       | Williams  | Finance          |
| 1001   | James       | Smith     | Human Resources  |
| 1002   | Maria       | Martinez  | Human Resources  |
| 1003   | Andrew      | Rodriguez | Human Resources  |
| 1004   | Donna       | Williams  | Human Resources  |
+--------+-------------+-----------+------------------+

As you can see above, a cross join is not so useful comparing the other joins. Since the SQL statement did not specify a condition, each row from the "Employee" is combined with each row from the "Department" table.

Note: A CROSS JOIN is not so often used in real-life scenario unless you are sure that you want a Cartesian product do not use a cross join.