SQL Cross Join

Using Cross Joins
If you don't specify a join condition when joining two tables, database system combines each row from the first table with each row from the second table. This type of join is called a cross join or a Cartesian product. The following Venn diagram illustrates how cross join works.

![SQL Cross Join Illustration](/uploads/images/20240424/b49f9bd15658e3b80ac133e5cba958c7.png "SQL Cross Join Illustration")

To understand this easily, let's look at the following employees and departments tables.

```sql
+--------+--------------+------------+---------+
| emp_id | emp_name     | hire_date  | dept_id |
+--------+--------------+------------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |       4 |
|      2 | Tony Montana | 2002-07-15 |       1 |
|      3 | Sarah Connor | 2005-10-18 |       5 |
|      4 | Rick Deckard | 2007-01-03 |       3 |
|      5 | Martin Blank | 2008-06-24 |    NULL |
+--------+--------------+------------+---------+
```
Table: employees
```sql
+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+
```
Table: departments

The number of rows in a cross join is the product of the number of rows in each table. Here's a simple example of a cross join operation.

```sql
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 CROSS JOIN departments AS t2;
```
<div class="callout callout-success mb-3">Tip: A cross join creates a Cartesian product or multiplication of all rows in one table with all rows in another. So, for example, if one table has 5 rows and another has 10 rows, a cross-join query produces 50 rows, the product of 5 and 10.</div>

After executing the above command, you get the result set something like this:

```sql
+--------+--------------+------------+------------------+
| emp_id | emp_name     | hire_date  | dept_name        |
+--------+--------------+------------+------------------+
|      1 | Ethan Hunt   | 2001-05-01 | Administration   |
|      2 | Tony Montana | 2002-07-15 | Administration   |
|      3 | Sarah Connor | 2005-10-18 | Administration   |
|      4 | Rick Deckard | 2007-01-03 | Administration   |
|      5 | Martin Blank | 2008-06-24 | Administration   |
|      1 | Ethan Hunt   | 2001-05-01 | Customer Service |
|      2 | Tony Montana | 2002-07-15 | Customer Service |
|      3 | Sarah Connor | 2005-10-18 | Customer Service |
|      4 | Rick Deckard | 2007-01-03 | Customer Service |
|      5 | Martin Blank | 2008-06-24 | Customer Service |
|      1 | Ethan Hunt   | 2001-05-01 | Finance          |
|      2 | Tony Montana | 2002-07-15 | Finance          |
|      3 | Sarah Connor | 2005-10-18 | Finance          |
|      4 | Rick Deckard | 2007-01-03 | Finance          |
|      5 | Martin Blank | 2008-06-24 | Finance          |
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources  |
|      2 | Tony Montana | 2002-07-15 | Human Resources  |
|      3 | Sarah Connor | 2005-10-18 | Human Resources  |
|      4 | Rick Deckard | 2007-01-03 | Human Resources  |
|      5 | Martin Blank | 2008-06-24 | Human Resources  |
|      1 | Ethan Hunt   | 2001-05-01 | Sales            |
|      2 | Tony Montana | 2002-07-15 | Sales            |
|      3 | Sarah Connor | 2005-10-18 | Sales            |
|      4 | Rick Deckard | 2007-01-03 | Sales            |
|      5 | Martin Blank | 2008-06-24 | Sales            |
+--------+--------------+------------+------------------+
```
As you can see a cross join is not as useful as the other joins that we've covered in the previous chapters. Since the query didn't specify a join condition, each row from the employees table is combined with each row from the departments table. Therefore, unless you are sure that you want a Cartesian product don't use a cross join.