MySQL五：多表查询

多表查询

一、笛卡尔积问题

1. 产生笛卡尔积的原因

   省略了多表的连接条件

   连接条件无效

   所有表中的所有行互相连接

2. 解决方法

   在WHERE子句中写入连接条件，如果各表中有相同的列，需在列名之前加上表名前缀

二、多表连接的规则

1. 数据库的查询，涉及到多个表，要对列名前加表的表名（或别名）
2. 连接n个表，至少需要n-1个连接条件
3. 如果在查询中使用了表的别名，就要在整个查询语句中全部使用别名

三、等值连接 vs 非等值连接

1. 等值连接：连接条件为等于
2. 非等值连接：连接条件为非等于的条件，例如：between … . and …

四、自连接 vs 非自连接

1. 如果table1和table2本质上是同一张表，只是用取别名的方式虚化成两张表以代表不同的意义，然后两个表再进行内连接、外连接等查询

五、内连接 vs 外连接

1. 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行

2. 外连接：两个表在连接过程中处理返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行，这种连接称为左（或右）连接，没有匹配的行时，结果表中相应的列为NULL。

3. SQL99中JOIN-ON子句创建连接的语法结构

   可以使用ON子句指定额外的连接条件，这个连接条件与其它条件是分开的

4. 内连接

   FROM A表 INNER JOIN B表 ON 连接条件

5. 左/右外连接

   左外连接：FROM A表 LEFT JOIN B表 ON 连接条件

   右外连接：FROM A表 RIGHT JOIN B表 ON 连接条件

6. 满外连接

   UNION：可以给出多条SELECT语句，并将它们的结果组合成单个结果集，合并时，各结果集对应的列数和数据类型必须相同。UNION返回的结果集是会去除重复记录的

   UNION ALL：用法与UNION相同，但是不会对结果集去重，因此执行效率更高，也更常用。

七、七种 JOINS 的实现

1. 左满外连接

   结果集是左表

   SELECT <select_list>
   FROM tableA A
   LEFT JOIN tableB B
   ON A.key = B.key;
   

2. 左空外连接

   结果是左表去重与右表的交集

   SELECT <select_list>
   FROM tableA A
   LEFT JOIN tableB B
   ON A.key = B.key
   WHERE B.key IS NULL;
   

3. 右满外连接

   结果集是右表

   SELECT <select_list>
   FROM tableA A
   RIGHT JOIN tableB B
   ON A.key = B.key
   

4. 右空外连接

   结果是右表去重与左表的交集

   SELECT <select_list>
   FROM tableA A
   RIGHT JOIN tableB B
   ON A.key = B.key
   WHERE A.key IS NULL;
   

5. 内实连接

   左表与右表的交集

   SELECT <select_list>
   FROM tableA A
   INNER JOIN tableB B
   ON A.key = B.key;
   

6. 内空连接

   左表与右表的满集去除两表的交集 （2+4）

   SELECT <select_list>
   FROM tableA A
   LEFT JOIN tableB B
   ON A.key = B.key
   WHERE B.key IS NULL
   UNION ALL
   SELECT <select_list>
   FROM tableA A
   RIGHT JOIN tableB B
   ON A.key = B.key
   WHERE A.key IS NULL;
   

7. 满外连接

   左表与右表的满集 （1+4 或 2+3）

   SELECT <select_list>
   FROM tableA A
   LEFT JOIN tableB B
   ON A.key = B.key
   UNION ALL
   SELECT <select_list>
   FROM tableA A
   RIGHT JOIN tableB B
   ON A.key = B.key
   WHERE A.key IS NULL;
   

八、SQL99新特性

1. NATURAL JOIN

   会自动将查询两张表中的所有同名字段，并进行自动连接

   # 正常JOIN ... ON ... AND ...
   SELECT e.last_name,e.department_id,d.department_name
   FROM employees e JOIN departments d
   ON e.department_id = d.department_id
   AND e.manager_id = d.manager_id;
   # 替换为 NATURAL JOIN
   SELECT e.last_name,e.department_id,d.department_name
   FROM employees e NATURAL JOIN departments d;
   

2. USING()

   对两张表中指定的同名字段进行连接，必须配合JOIN一起使用，但是不适用于自连接（自连接中的连接使用的字段名不相同）

   # 正常JOIN. . . ON . . 
   SELECT e.last_name,e.department_id,d.department_name
   FROM employees e JOIN departments d
   ON  e.department_id = d.department_id
   
   # 替换为USING()
   SELECT e.last_name,e.department_id,d.department_name
   FROM employees e JOIN departments d
   USING(department_id) ;