如果在连接两个表时没有指定连接条件,则数据库系统将第一个表中的每一行与第二个表中的每一行组合起来。这种类型的连接称为交叉连接或笛卡尔积。
下图说明了交叉连接的工作原理。
为了更容易理解这一点,让我们看一下下面的employees和departments表。
employees
+--------+--------------+------------+---------+
| 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 |
+--------+--------------+------------+---------+
departments
+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
+---------+------------------+
交叉连接中的行数是每个表中的行数的乘积。下面是一个交叉连接的简单示例。
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 CROSS JOIN departments AS t2;
交叉连接创建一个笛卡尔积,即一个表中的所有行与另一个表中的所有行相乘。例如,如果一个表有5行,另一个表有10行,交叉连接查询生成50行,即5和10的乘积。
执行上述命令后,得到的结果集如下:
+--------+--------------+------------+------------------+
| 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 |
+--------+--------------+------------+------------------+
交叉连接不如我们在前几章中介绍的其他连接有用,由于查询没有指定连接条件,所以employees表中的每一行都与departments表中的每一行相结合。因此,除非您确定要使用笛卡尔积,否则不要使用交叉连接。