0. 多表查询

• 多表查询(关联查询)：两个或更多个表一起完成查询操作。
• 前提条件：表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。
  • 比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

1. 笛卡尔积

1.1 案例：查询员工的姓名及其部门名称

SELECT last_name, department_name FROM employees, departments;

• 查询结果发现每个名字都对应了每个部门名

1.2 笛卡尔积

• 笛卡尔积 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能 组合
• 笛卡尔积也称为 交叉连接 (CROSS JOIN)
• 它的作用就是可以把任意表进行连接，即使这两张表不相关
• 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

2. 多表查询分类

• 分类1： 等值连接 VS 非等值连接
  • 等值连接

   SELECT e.last_name, d.department
  FROM employees e, departments j
  WHERE e.department = d.department
  

  • 非等值连接示例

  SELECT e.last_name, e.salary, j.grade_level
  FROM employees e, job_grades j
  WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
  

• 分类2： 自连接 VS 非自连接
  • 自连接：即连接的两张表是同一张表 查询员工为哪个经理工作

  SELECT CONCAT(worker.last_name ,' works for ' , manager.last_name)
  FROM employees worker, employees manager
  WHERE worker.manager_id = manager.employee_id ;
  

• 分类3： 内连接 VS 外连接
  • 内连接: 合并具有同一列 的表 的行, 结果集中不包含一个表与另一个表不匹配的行
  • 外连接: 除了返回内连接的结果外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为NULL。

3. SQL99 语法实现多表查询

3.1 基本用法

SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件

• 可以使用 ON 子句指定额外的连接条件。
• 这个连接条件是与其它条件分开的。
• ON 子句使语句具有更高的易读性。
• 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

3.2 外连接的实现

• 左外连接的实现， 右外连接类似

-实现查询结果是A
SELECT 字段列表
FROM 主表 LEFT JOIN 从表
ON 关联条件
WHERE 等其他子句;

3.3 满外连接

• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
• MySQL 不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN 代替。

4. UNION 的使用

• 给出多条SELECT语句，并将它们的结果组合成单个结果集。
• 合并时，两个表对应的列数和数据类型必须相同，并且相互对应。

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

• UNION 操作符返回两个查询的结果集的并集，去除重复记录。
• UNION ALL 不去重。

注意：如果知道不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

5. 七种SQL JOIN的实现

#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

#左下图：满外连接
# 左中图 + 右上图 A∪B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

6. SQL99 新特性

6.1 自然连接 NATRUAL JOIN

• 自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接 。

6.2 USING 连接

对 JOIN ON 的简化

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

可变为

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

7. 章节小结

• 表连接的约束条件可以有三种方式：WHERE, ON, USING
  • WHERE：适用于所有关联查询
  • ON ：只能和JOIN一起使用，比WHERE 可读性好
  • USING：只能和JOIN一起使用，要求两个关联字段在关表中名称一致，而且只能表示关联字段值相等

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。 说明：即使双表 join 也要注意表索引、SQL 性能。

阿里开发规范 ： 【 强制 】对于数据库中表记录的操作，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。 说明 ：如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。
正例 ：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;
反例 ：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在 某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出 1052 异常：Column 'name' in field list is ambiguous。