合并查询语句

合并多条 SELECT 语句的结果，合并时，两个表对应的列数和数据类型必须相同，且相互对应。

• UNION

  返回两个查询的结果集的并集，会去除重复记录。

• UNION ALL

  返回两个查询的结果集的并集，对于重复记录，不去重。执行 UNION ALL 语句所需的资源比 UNION 语句少，如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复 数据时，尽量使用 UNION ALL 语句，提高数据查询的效率。

内连接

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

左外连接

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

右外连接

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

左外连接时左表不匹配的行

SELECT e.employee_id, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

右外连接时右表不匹配的行

SELECT e.employee_id, d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

满外连接

# 左外连接
SELECT e.employee_id, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
# 右外连接时右表不匹配的行
SELECT e.employee_id, d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

# 左外连接时左表不匹配的行
SELECT e.employee_id, d.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 e.employee_id, d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

左外连接时左表不匹配的行和右外连接时右表不匹配的行

SELECT e.employee_id, d.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 e.employee_id, d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

SQL99 语法的新特性

• NATURAL JOIN

  可以理解为 SQL92 中的等值连接，会帮你自动查询两张连接表中所有相同的字段，然后进行 等值连接。

  # SQL92
  SELECT emp.employee_id, emp.last_name, dep.department_name
  FROM employees emp JOIN departments dep
  ON emp.department_id = dep.department_id
  AND emp.manager_id = dep.manager_id;
  
  # SQL99
  SELECT emp.employee_id, emp.last_name, dep.department_name
  FROM employees emp NATURAL JOIN departments dep;

• USING JOIN

  指定数据表里的同名字段进行等值连接，只能配置 JOIN 一起使用。

  SELECT emp.employee_id, emp.last_name, dep.department_name
  FROM employees emp JOIN departments dep
  ON emp.department_id = dep.department_id;
  
  SELECT emp.employee_id, emp.last_name, dep.department_name
  FROM employees emp JOIN departments dep
  USING (department_id);