目录

• 1. 多表查询示例
• 2. 多表查询分类
• • 2.1 等/非等值连接
  • • 2.1.1 等值连接
    • 2.1.2非等值连接
  • 2.2 自然/非自然连接
  • 2.3 内/外连接
  • • 2.3.1 内连接
    • 2.3.2 外连接
• 3.UNION的使用
• • 3.1 合并查询结果
  • • 3.1.1 UNION操作符
    • 3.1.2 UNION ALL操作符
• 4. 7种JOIN操作
• 5. join 多张表

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

• 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。
  • SELECT tb_a.id, tb_b.id FROM tb_a,tb_b WHERE tb_a.id=tb_b.id;
• 可以给表起别名，在SELECT和WHERE中使用表的别名
  • SELECT t1.id, t2.id FROM tb_a as t1,tb_b as t2 WHERE t1.id=t2.id;
• 如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不是使用表的原名。
• 如果有n个表实现多表的查询，则需要至少n-1个连接条件

1. 多表查询示例

新建表：

SELECT e.employee_id as '员工id',e.last_name as '员工名称',d.department_name as '员工所在部门',l.city as '部门所在城市'
FROM employees e, departments d, locations l
WHERE e.department_id=d.department_idAND d.location_id=l.location_id;

结果：

2. 多表查询分类

2.1 等/非等值连接

2.1.1 等值连接

SELECT employees.employee_id as '员工id',employees.last_name as '员工名称',employees.department_id as '部门id',departments.department_id as '部门id',departments.department_name as '部门名称',departments.location_id as '位置id'
FROM employees, departments
WHERE employees.department_id = departments.department_id;

2.1.2非等值连接

SELECT e.last_name as '员工名称',e.salary as '员工薪资',j.grade_level as '员工级别'
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
-- 或者
WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;

2.2 自然/非自然连接

SELECT emp.employee_id as '员工id', emp.last_name as '员工名称', mgr.employee_id as '管理者id', mgr.last_name as '管理者名称'
FROM employees emp, employees mgr
WHERE emp.manager_id = mgr.employee_id;
或者
SELECT CONCAT(worker.last_name ,' works for ' , manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

2.3 内/外连接

• 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
• 外连接: 两个表在连接过程中除了返回满足连接条件的行以外**还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。**没有匹配的行时, 结果表中相应的列为空(NULL)。
• 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
• 如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。

2.3.1 内连接

SELECT e.employee_id, d.department_id, l.city
FROM employees eINNER JOIN departments dON e.department_id = d.department_idINNER JOIN locations lON d.location_id = l.location_id;-- 返回 employees_id和department_id都不为NULL的数据

2.3.2 外连接

-- 左外连接
SELECT e.employee_id, d.department_id
FROM employees eLEFT OUTER JOIN departments dON e.department_id = d.department_id;-- 右外连接SELECT e.employee_id, d.department_id
FROM employees eRIGHT OUTER JOIN departments dON e.department_id = d.department_id;

3.UNION的使用

3.1 合并查询结果

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

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

3.1.1 UNION操作符

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

3.1.2 UNION ALL操作符

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

select 'newMember' as "memberType", count(distinct full_union_id) as "memberCount" 
from XXX.XXX  
where XX_id = cast(66666666 as varchar) and XX_time >= '2021-06-16 00:00:00' and XX_time <= '2022-06-16 00:00:00' union allselect 'oldMember' as "memberType", count(distinct full_union_id) as "memberCount" 
from XXX.XXX  
where XX_id = cast(66666666 as varchar) and XX_time <'2021-06-16 00:00:00' 

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

4. 7种JOIN操作

中图：内连接

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

左上图：左外连接

SELECT employee_id, last_name, department_name
FROM employees eLEFT JOIN departments dON e.department_id = d.department_id;

右上图：右外连接

SELECT employee_id,last_name,department_name
FROM employees eRIGHT JOIN departments dON e.department_id = d.department_id;

左中图

SELECT employee_id, last_name, department_name
FROM employees eLEFT JOIN departments dON e.department_id = d.department_id
WHERE d.department_id IS NULL;

右中图

SELECT employee_id, last_name, department_name
FROM employees eRIGHT JOIN departments dON e.department_id = d.department_id
WHERE e.department_id IS NULL;

左下图： 左中图 + 右上图

SELECT employee_id,last_name,department_name
FROM employees eLEFT JOIN departments dON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees eRIGHT JOIN departments dON e.department_id = d.department_id;

右下图：左中图 + 右中图

SELECT employee_id,last_name,department_name
FROM employees eLEFT JOIN departments dON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees eRIGHT JOIN departments dON e.department_id = d.department_id
WHERE e.department_id IS NULL

5. join 多张表

SELECT B.id, A.sid, C.c_type, C.c_name, D.sku_id
FROM db_name.table_name_a ARIGHT JOIN db_name.table_name_b B ON A.id = B.idRIGHT JOIN db_name.table_name_c C ON  B.channel_id  = C.channel_idRIGHT JOIN db_name.table_name_d D ON A.id = D.oid
WHERE A.code = 16AND A.order_status != 4AND A.create_time > '2023-03-08 21:00:00'AND A.tid = '1234567890'
ORDER BY A.create_time DESC 
LIMIT 200;

注意：

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

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、SQL 性能。

来源：阿里巴巴《Java开发手册》