【MySQL】多表查询(JOIN / UNION)

RiceVan

已于 2023-06-02 11:43:10 修改

阅读量8.3k

点赞数 8

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2023-02-07 17:04:58 首次发布

本文链接：https://blog.csdn.net/RiceVan/article/details/128907870

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

本文详细介绍了多表查询的概念和不同类型的连接操作，包括笛卡尔积错误及如何通过WHERE子句避免，等值连接，非等值连接，自连接，内连接（INNERJOIN）和外连接（LEFTJOIN）。还讨论了连接查询的注意事项，如限制连接表数量，保持字段一致性和使用索引。此外，文章提供了多种JOIN的实现示例和相关练习题，帮助读者理解和掌握多表查询技巧。

摘要由CSDN通过智能技术生成

文章目录

- 多表查询

多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。

1. 笛卡尔积错误

X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，组合的个数即为两个集合中元素个数的乘积数。
笛卡尔积也称为交叉连接 CROSS JOIN 。作用就是可以把任意表进行连接，即使这两张表不相关。
直接进行多表查询，不添加连接条件会出现笛卡尔积错误：

#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees, departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;
# 结果会将last_name所有值和department_name所有值做排列组合

在 WHERE子句中写入连接条件，可以避免出现笛卡尔积情况。

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件

2. 等值连接 WHERE

多个表中有相同列时，必须在列名之前加上表名前缀。
使用别名可以简化查询，AS可省略。
列名前使用表名前缀可以提高查询效率。
如果使用了表的别名，在查询字段中、过滤条件中就只能使用别名，不能使用原有的表名，否则就会报错。
- 阿里开发规范：【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。否则后续多表新增同名字段时，导致ambiguous模糊异常。
等值连接 n个表,至少需要n-1个连接条件。

# 表名前缀
SELECT employees.employee_id, employees.last_name, employees.department_id, departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

# 别名
SELECT e.last_name, d.department_id, e.department_id
FROM employees e, departments AS d 
WHERE e.department_id = d.department_id;

# 连接多个表,连接 n个表,至少需要n-1个连接条件。
SELECT e.employee_id, e.last_name, e.department_id, d.department_name, d.location_id, l.location_id, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id
ORDER BY e.employee_id, l.location_id;

3. 非等值连接

# 非等值连接
SELECT e.last_name, e.salary, j.grade_level
FROM employees AS e, job_grades AS j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
ORDER BY grade_level;

4. 自连接

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

# CONCAT() 可实现字段和字符串拼接
SELECT CONCAT (worker.last_name,' works for ', manager.last_name)
FROM employees worker, employees manager 
WHERE worker.manager_id = manager.manager_id;

5. 内连接 INNER JOIN

内连接: 两表连接查询时，连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。不包含不匹配的行。
内连接通过 INNER JOIN 表名 ON 条件 实现，INNER 可省略。
语法：

# 内连接(INNER JOIN)的实现
SELECT 字段列表
FROM A表 
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;
# 效果相同：
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;

# 例二
SELECT employee_id, city, department_name
FROM employees e 
JOIN departments d 
ON d.department_id = e.department_id
JOIN locations l 
ON d.location_id = l.location_id;
# 效果相同
SELECT employee_id, city, department_name
FROM employees e, departments d, locations l 
WHERE e.department_id = d.department_id 
AND d.location_id = l.location_id;

6. 外连接 LEFT JOIN

外连接：两表连接查询时，连接结果不仅包含符合连接条件的行，同时也包含自身不符合条件的行。没有匹配的行时, 结果表中相应的列为空(NULL)。
外连接分为左外连接、右外连接，通过 LEFT / RIGHT OUTER JOIN 表名 ON 条件 实现，OUTER 可省略。
如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。
结果包括主表的所有行，从表不满足条件的行内容为NULL。
MySQL不支持满外连接，可以通过UNION实现。

#左外连接
SELECT 字段列表
FROM A表 
LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

#右外连接
SELECT 字段列表
FROM A表 
RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

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

结果包括employees表的全部行，employees表中的Grant没有department_id，departments表中无对应数值，仍显示在结果中（NULL）。

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

结果包括departments表的全部行，employees表中无对应department_id，仍显示在结果中（NULL）。

7. 合并查询 UNION

利用UNION关键字，可以同时查询多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。
各个SELECT语句之间使用 UNION 或 UNION ALL 关键字分隔。
UNION ：返回查询的结果集不包括重复记录。
UNION ALL：返回查询的结果集包括重复记录。
执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

SELECT column,... FROM table1
UNION
SELECT column,... FROM table2

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

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

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

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

8. 注意

多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此要控制连接表的数量，不连接不必要的表。
在许多 DBMS 中，也都会有最大连接表的限制。
阿里【强制】：超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。即使双表 join 也要注意表索引、SQL 性能。

9. 七种JOIN的实现

1、
在这里插入图片描述

# A ∩ B
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
JOIN departments d 
ON e.department_id = d.department_id;

2、
在这里插入图片描述

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

3、
在这里插入图片描述

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

4、
在这里插入图片描述

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

6、
在这里插入图片描述

# A∪B 
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT JOIN departments d 
ON e.department_id = d.department_id
UNION 
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT JOIN departments d 
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

7、
在这里插入图片描述

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

10.练习

# 1.显示所有员工的姓名，部门号和部门名称。
-- 1.1等值连接WHERE，只显示满足连接条件的结果
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
-- 1.2内连接join on，只显示满足连接条件的结果，1/2方法等效
SELECT last_name, e.department_id, d.department_name
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id;
-- 1.3外连接left join，显示主表左表的全部结果
-- 因为存在无主管的员工（deptid和deptname填null），结果比1/2更充分
SELECT last_name, e.department_id, d.department_name
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.department_id;

# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT e.department_id, e.job_id, d.location_id
FROM employees e, departments d
WHERE e.department_id = 90 
AND e.department_id = d.department_id;

-- 先通过join on 连接两表，再添加筛选条件
SELECT e.department_id, e.job_id, d.location_id
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id
WHERE e.department_id = 90;

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT last_name, d.department_name, d.location_id, city 
FROM employees e, departments d, locations l 
WHERE e.commission_pct IS NOT NULL 
AND e.department_id = d.department_id
AND d.location_id = l.location_id; 

SELECT last_name, d.department_name, d.location_id, city 
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id
JOIN locations l 
ON d.location_id = l.location_id
WHERE commission_pct IS NOT NULL;

# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e, departments d, locations l 
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.city = 'Toronto';

SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id
JOIN locations l 
ON d.location_id = l.location_id
WHERE l.city = 'Toronto';

#5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salary
FROM employees e, departments d, locations l 
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND d.department_name = 'Executive';

SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salary
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id
JOIN locations l 
ON d.location_id = l.location_id
WHERE d.department_name = 'Executive';

#6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employees Emp# manager Mgr#kochhar 101 king 100
SELECT e1.last_name, e1.employee_id, e1.manager_id, e2.last_name manager_name 
FROM employees e1, employees e2 
WHERE e1.manager_id = e2.employee_id;

SELECT e1.last_name, e1.employee_id, e1.manager_id, e2.last_name manager_name 
FROM employees e1 
JOIN employees e2 
ON e1.manager_id = e2.employee_id;

-- left join on 会包含employee没有manager的结果
SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager,
mgr.employee_id "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;


# 7.查询哪些部门没有员工
SELECT d.department_id, d.department_name
FROM departments d 
LEFT JOIN employees e 
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;

SELECT d.department_id, d.department_name
FROM departments d 
WHERE NOT EXISTS (
					SELECT * FROM employees e
					WHERE e.department_id = d.department_id);

# 8. 查询哪个城市没有部门
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;

# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT e.last_name, e.employee_id, e.department_id, d.department_name
FROM employees e, departments d 
WHERE e.department_id = d.department_id
AND d.department_name IN ('Sales', 'IT');