持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第11天，点击查看活动详情
SQL99语法实现多表的查询
1.基本语法
使用JOIN...ON...字句创建连接的语法结构：
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
嵌套逻辑类似于FOR循环：
for t1 in table1: for t2 in table2:
if condition1:
for t3 in table3: if condition2:
output t1 + t2 + t3
2.内连接（INNER JOIN）的实现
语法：
# 内连接（INNER JOIN）的语法
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
题目1：
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）的实现
1.左外连接（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) ;
2.右外连接（RIGHT OUTER JOIN）
语法：
# 实现查询结果是B
SELECT 字段列表
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);
3.满外连接（FULL OUTER JOIN）
MySQL不支持FULL OUTER JOIN
SELECT employee_id,department_name,city
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
UNION的使用
合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。 语法：
SELECT column,... FROM table1 UNION [ALL]
SELECT column,... FROM table2
UNION操作符：返回两个查询的结果集的并集，去除重复记录。
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;
7种SQL JOINS的实现
# 图1的实现：左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
# 图2的实现：右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;
# 图3的实现：内连接 A∩B
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
# 图4的实现：在图1的基础上抹除相同的部分 A - A∩B
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE D.department_id IS NULL;
# 图5的实现 B-A∩B
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
# 图6的实现：满外连接
-- 方式1：图6 = 图1 UNION ALL 图5
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
-- 方式2：图6 = 图4 UNION ALL 图2
SELECT employee_id,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,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;
# 图7的实现：图6 = 图4 UNION ALL 图5
SELECT employee_id,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,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
获取testdb.sql
链接：pan.baidu.com/s/1IPjM6E6Z… 提取码：1234
