MySQL中的多表查询②

SQL99语法实现多表查询

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

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

注意点：
①关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接。
②外连接的OUTER可以省略
③使用SQL99语法实现多表查询更易读，每加一张表，那么就多一个JOIN ··ON··结构
④MySQL不支持FULL OUTER JOIN(满外连接)

例子：

#SQL99语法实现内连接：
SELECT last_name,department_name
FROM employees e 
INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

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

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

#满外连接：mysql不支持FULL OUTER JOIN
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

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语句，以提高数据查询的效率。

举例：
下面通过具体示例说明如何在 MySQL 中使用 UNION 进行多表查询。

假设我们有两个表：

• students（学生表）：包含 id、name、age 字段
• teachers（教师表）：包含 id、name、age 字段

如果我们想获取所有学生和教师的姓名与年龄，可以使用 UNION 合并两个查询结果：

-- 使用UNION查询所有学生和教师的姓名与年龄（自动去重）
SELECT name, age, 'student' AS type FROM students
UNION
SELECT name, age, 'teacher' AS type FROM teachers
ORDER BY age;

-- 使用UNION ALL查询所有学生和教师的姓名与年龄（保留重复项）
SELECT name, age, 'student' AS type FROM students
UNION ALL
SELECT name, age, 'teacher' AS type FROM teachers
ORDER BY age;

上述示例中：

1. 两个 SELECT 语句返回相同数量的列（name、age、type）
2. 新增了 type 字段用于区分记录来自学生表还是教师表
3. 最后使用 ORDER BY 对合并后的结果按年龄排序

UNION 会自动去除两个结果集中的重复记录，而 UNION ALL 则会保留所有记录（包括重复项），执行效率也更高。

使用时需要注意：

• 所有 SELECT 语句的列数和顺序必须一致
• 对应列的数据类型必须兼容
• 可以在最后一个查询后使用 ORDER BY 对整体结果排序

7种SQL JOINS的实现(使用SQL99语法)

同样将employees表中的员工id，和departments表中的部门相连接
代码实现：

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

SQL99语法新特性

自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接 。
例如：
在SQL92标准中：

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

在 SQL99 中你可以写成：

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

缺点：
自然连接会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接，这就失去了灵活性。比如还是上面的例子若两表连接只需要满足 e.department_id = d.department_id 用自然连接是表示不出来的。

USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。比如：
在SQL92标准中：

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

在 SQL99 中你可以写成：

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

缺点：自连接无法用USING

开发注意

我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

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