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

文章目录

• • 多表查询
  • • 1. 笛卡尔积错误
    • 2. 等值连接 WHERE
    • 3. 非等值连接
    • 4. 自连接
    • 5. 内连接 INNER JOIN
    • 6. 外连接 LEFT JOIN
    • 7. 合并查询 UNION
    • 8. 注意
    • 9. 七种JOIN的实现
    • 10.练习

多表查询

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

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.columnFROM table1, table2WHERE table1.column1 = table2.column2; #连接条件

2. 等值连接 WHERE

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

# 表名前缀SELECT employees.employee_id, employees.last_name, employees.department_id, departments.location_idFROM employees, departmentsWHERE employees.department_id = departments.department_id;# 别名SELECT e.last_name, d.department_id, e.department_idFROM 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.cityFROM employees e, departments d, locations lWHERE e.department_id = d.department_id AND d.location_id = l.location_idORDER BY e.employee_id, l.location_id;

3. 非等值连接

# 非等值连接SELECT e.last_name, e.salary, j.grade_levelFROM employees AS e, job_grades AS jWHERE e.salary BETWEEN j.lowest_sal AND j.highest_salORDER 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_idFROM employees e JOIN departments dON e.department_id = d.department_id;# 效果相同：SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_idFROM employees e, departments d WHERE e.department_id = d.department_id;# 例二SELECT employee_id, city, department_nameFROM employees e JOIN departments d ON d.department_id = e.department_idJOIN locations l ON d.location_id = l.location_id;# 效果相同SELECT employee_id, city, department_nameFROM 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_nameFROM 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_nameFROM 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 table1UNIONSELECT column,... FROM table2SELECT column,... FROM table1UNION ALLSELECT column,... FROM table2

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

SELECT * FROM employeesWHERE email LIKE '%a%' OR department_id > 90;SELECT * FROM employeesWHERE email LIKE '%a%'UNION SELECT * FROM employeesWHERE department_id > 90;

• 查询中国用户中男性的信息以及美国用户中年男性的用户信息

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

8. 注意

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

9. 七种JOIN的实现

# A ∩ BSELECT e.last_name, e.department_id, d.department_nameFROM employees eJOIN departments d ON e.department_id = d.department_id;

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

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

# A - A∩BSELECT e.last_name, e.department_id, d.department_nameFROM employees e LEFT JOIN departments d ON e.department_id = d.department_idWHERE d.department_id IS NULL;

# B-A∩BSELECT e.last_name, e.department_id, d.department_nameFROM employees e RIGHT JOIN departments d ON e.department_id = d.department_idWHERE e.department_id IS NULL;

# A∪B SELECT e.last_name, e.department_id, d.department_nameFROM employees eLEFT JOIN departments d ON e.department_id = d.department_idUNION SELECT e.last_name, e.department_id, d.department_nameFROM employees eRIGHT JOIN departments d ON e.department_id = d.department_idWHERE e.department_id IS NULL;

SELECT e.last_name, e.department_id, d.department_nameFROM employees eLEFT JOIN departments d ON e.department_id = d.department_idWHERE d.department_id IS NULLUNIONSELECT e.last_name, e.department_id, d.department_nameFROM employees eRIGHT JOIN departments d ON e.department_id = d.department_idWHERE e.department_id IS NULL

10.练习

# 1.显示所有员工的姓名，部门号和部门名称。-- 1.1等值连接WHERE，只显示满足连接条件的结果SELECT e.last_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id = d.department_id;-- 1.2内连接join on，只显示满足连接条件的结果，1/2方法等效SELECT last_name, e.department_id, d.department_nameFROM 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_nameFROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;# 2.查询90号部门员工的job_id和90号部门的location_idSELECT e.department_id, e.job_id, d.location_idFROM employees e, departments dWHERE e.department_id = 90 AND e.department_id = d.department_id;-- 先通过join on 连接两表，再添加筛选条件SELECT e.department_id, e.job_id, d.location_idFROM employees e JOIN departments d ON e.department_id = d.department_idWHERE e.department_id = 90;# 3.选择所有有奖金的员工的 last_name , department_name , location_id , citySELECT 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_idAND 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_idJOIN locations l ON d.location_id = l.location_idWHERE commission_pct IS NOT NULL;# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_nameSELECT e.last_name, e.job_id, e.department_id, d.department_nameFROM employees e, departments d, locations l WHERE e.department_id = d.department_idAND d.location_id = l.location_idAND l.city = 'Toronto';SELECT e.last_name, e.job_id, e.department_id, d.department_nameFROM employees e JOIN departments d ON e.department_id = d.department_idJOIN locations l ON d.location_id = l.location_idWHERE l.city = 'Toronto';#5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salaryFROM employees e, departments d, locations l WHERE e.department_id = d.department_idAND d.location_id = l.location_idAND d.department_name = 'Executive';SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salaryFROM employees e JOIN departments d ON e.department_id = d.department_idJOIN locations l ON d.location_id = l.location_idWHERE d.department_name = 'Executive';#6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employees Emp# manager Mgr#kochhar 101 king 100SELECT 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 empLEFT OUTER JOIN employees mgrON emp.manager_id = mgr.employee_id;# 7.查询哪些部门没有员工SELECT d.department_id, d.department_nameFROM departments d LEFT JOIN employees e ON d.department_id = e.department_idWHERE e.department_id IS NULL;SELECT d.department_id, d.department_nameFROM departments d WHERE NOT EXISTS (SELECT * FROM employees eWHERE e.department_id = d.department_id);# 8. 查询哪个城市没有部门SELECT l.location_id, l.city FROM locations lLEFT JOIN departments d ON l.location_id = d.location_idWHERE d.location_id IS NULL;# 9. 查询部门名为 Sales 或 IT 的员工信息SELECT e.last_name, e.employee_id, e.department_id, d.department_nameFROM employees e, departments d WHERE e.department_id = d.department_idAND d.department_name IN ('Sales', 'IT');

来源地址：https://blog.csdn.net/RiceVan/article/details/128907870