多表查询

多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个

关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进

#案例：查询员工的姓名及其部门名称 
SELECT last_name, department_name FROM employees, departments;

笛卡尔积（或交叉连接）的理解

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能

组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素

SQL92中，笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交

叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡

-- 查询员工姓名和所在部门的名称 
select last_name,department_name from departments,employees;

SQL99语法实现多表查询

使用JOIN...ON子句创建连接的语法结构：

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

内连接(INNER JOIN)的实现

语法：

SELECT 字段列表 FROM A表 INNER JOIN B表 ON 关联条件 WHERE 等其他子句;

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id);

外连接(OUTER JOIN)的实现

左外连接(LEFT OUTER JOIN)

语法：

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

SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ;

右外连接(RIGHT OUTER JOIN)

语法：

FROM A表 RIGHT JOIN B表 ON 关联条件 WHERE 等其他子句;

SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ;

满外连接(FULL OUTER JOIN)

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。 SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。

需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替

UNION的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION

ALL关键字分隔。

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

UNION操作符

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

UNION ALL操作符

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据

不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效

率。

举例：查询部门编号>90或邮箱包含a的员工信息

• 方式1

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

• 方式2

`SELECT * FROM employees WHERE email LIKE '%a%'`

`UNION`

`SELECT * FROM employees WHERE department_id>90;`

• 举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息

`SELECT id,cname FROM t_chinamale WHERE csex='男'`

`UNION ALL`

`SELECT id,tname FROM t_usmale WHERE tGender='male';`

7种SQL JOINS的实现

代码实现

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

#左上图：左外连接 
ELECT 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

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

语法格式小结

#实现A - A∩B 
select 字段列表 from A表 left join B表 on 关联条件 where 从表关联字段 is null and 等其他子句;

#实现B - A∩B 
select 字段列表 from A表 right join B表 on 关联条件 where 从表关联字段 is null and 等其他子句;

#实现查询结果是A∪B #用左外的A，union 右外的B select 字段列表 from A表 left join B表 on 关联条件 where 等其他子句 union select 字段列表 from A表 right join B表 on 关联条件 where 等其他子句;

#实现A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B） 
#使用左外的 (A - A∩B) union 右外的（B - A∩B） 
select 字段列表 from A表 left join B表 on 关联条件 where 从表关联字段 is null and 等其他子句 union select 字段列表 from A表 right join B表 on 关联条件 where 从表关联字段 is null and 等其他子句

#中图：内连接 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;