If you don't provide a join condition when combining two tables, the database system pairs each row from the first table with every row from the second table. This type of join is known as a cross join or a Cartesian product. The following Venn diagram demonstrates how a cross join works.
To easily grasp this concept, let's look at the following employees and departments tables.
+--------+--------------+------------+---------+ | 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 | +--------+--------------+------------+---------+ |
+---------+------------------+ | dept_id | dept_name | +---------+------------------+ | 1 | Administration | | 2 | Customer Service | | 3 | Finance | | 4 | Human Resources | | 5 | Sales | +---------+------------------+ |
|
Table: employees |
Table: departments |
The total number of rows in a cross join is the product of the number of rows in each table. Here is a straightforward example of a cross join operation.
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 CROSS JOIN departments AS t2;
Tip: A cross join generates a Cartesian product, multiplying all rows in one table by all rows in another. For instance, if one table has 5 rows and the other has 10 rows, a cross join query will result in 50 rows, which is the product of 5 and 10.
After running the command above, you'll get a result set similar to this:
+--------+--------------+------------+------------------+ | 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 isn't as practical as the other joins we've discussed in earlier chapters. Because the query didn't specify a join condition, each row from the employees table is paired with each row from the departments table. So, unless you specifically need a Cartesian product, avoid using a cross join.