多表查询

1. 多表查询分类

1）笛卡尔积（或交叉连接）

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

Untitled (1)

2) 等值连接

SELECT employees.employee_id， employees.last_name， employees.department_id， departments.department_id， departments.location_id FROM employees， departments 
WHERE employees.department_id = departments.department_id;

1
2

区分重复的列名

多个表中有相同列时，必须在列名之前加上表名前缀。在不同表中具有相同列名的列可以用表名加以区分。

SELECT employees.last_name， departments.department_name，employees.department_id 
FROM employees， departments 
WHERE employees.department_id = departments.department_id;

1
2
3

表的别名

使用别名可以简化查询。列名前使用表名前缀可以提高查询效率。

SELECT e.employee_id， e.last_name， e.department_id， d.department_id， d.location_id 
FROM employees e ， departments d 
WHERE e.department_id = d.department_id;

1
2
3

需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

阿里开发规范：

【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。

说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。

正例：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。

3) 非等值连接

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;

1
2
3

4) 自连接

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

题目：查询employees表，返回 <员工 works for 老板>

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

1
2
3

5) 内连接与外连接

基本语法

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

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

1
2
3
4

它的嵌套逻辑类似我们使用的 FOR 循环：

for t1 in table1:
 for t2 in table2:
  if condition1:
   for t3 in table3:
    if condition2:
     output t1 + t2 + t3

1
2
3
4
5
6

语法说明：

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

内连接

内连接: 合并具有同一列的两个以上的表的行， 结果集中不包含一个表与另一个表不匹配的行

SQL92语法

SELECT emp.employee_id,dep.department_name
FROM employee emp,department dep
WHERE emp.`department_id` = dep.`department_id`;

1
2
3

SQL99语法

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

SELECT emp.employee_id,dep.department_name
FROM employee emp JOIN department dep
ON emp.`department_id` = dep.`department_id`;

1
2
3
4
5
6
7
8

外连接

外连接: 两个表在连接过程中除了返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时，结果表中相应的列为空(NULL)。
如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。

LEFT OUTER JOIN

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

SELECT last_name,department_name
FROM employees emp LEFT OUTER JOIN department dep
ON emp.`department_id` = dep.`department_id`;

1
2
3
4
5
6
7
8
9

如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。

RIGHT OUTER JOIN

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

SELECT last_name,department_name
FROM employees emp RIGHT OUTER JOIN department dep
ON emp.`department_id` = dep.`department_id`;

1
2
3
4
5
6
7
8
9

6) 满外连接(FULL OUTER JOIN)

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

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

2. UNION的使用

合并查询结果

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

语法格式：

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

1
2
3

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;

1
2

#方式2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

1
2
3
4

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

SELECT id，cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id，tname FROM t_usmale WHERE tGender='male';

1
2
3

3.七种SQL JOINS的实现

Untitled

# 中图：内连接
SELECT employee_id，department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

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

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

# 左中图：
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;

# 右中图：
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;


# 左下图：满外连接
# 方式1：左上图 UNION ALL 右中图
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：左中图 UNION ALL 右上图
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`;

# 右下图：左中图  UNION ALL 右中图
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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

4. SQL99语法的新特性

1) 自然连接

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`;

1
2
3
4

在 SQL99 中你可以写成：

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

1
2

2) USING连接

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

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

1
2
3

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的：

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

1
2
3

5. 小结

表连接的约束条件可以有三种方式：WHERE， ON， USING

WHERE：适用于所有关联查询
ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

我们要控制连接表的数量。

多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。

在许多 DBMS 中，也都会有最大连接表的限制。

# 习题巩固
# 注意：当两个表外连接之后，组成主表和从表，主表的连接字段是不为空的，从表的连接字段可能为空，因此从表的关键字段用来判断是否为空。

# 1.查询哪些部门没有员工
# 方式一
SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`department_id` IS NULL;

# 方式二
SELECT department_id
FROM departments d
WHERE NOT EXISTS (
  SELECT *
     FROM employees e
     WHERE e.`department_id` = d.`department_id`
);

# 2.查询哪个城市没有部门
SELECT l.location_id， l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;

# 3.查询部门名为 Sales 或 IT 的员工信息
SELECT e.employee_id， e.last_name， e.department_id
FROM employees e JOIN department d
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` IN ('Sales'， 'IT');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

#MySQL

上次更新: 2025/02/26, 08:57:57

← 排序与分页函数→