SQL多表查询

一、概述：多表查询就是指从多张表中查询数据。

二、 分类 连接查询

1、内连接:相当于查询A、B交集部分数据

1). 隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
2). 显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

案例: 
A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现) 表结构: emp , dept
连接条件: emp.dept_id = dept.id
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ... ON ...
表结构: emp , dept
连接条件: emp.dept_id = dept.id

select e.name, d.name from emp e （inner） join dept d  on e.dept_id = d.id;
-- 为每一张表起别名,简化SQL编写
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

2、左外连接:查询左表所有数据，以及两张表交集部分数据

外连接分为两种，分别是:左外连接 和 右外连接。具体的语法结构为: 
1). 左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

案例:
A. 查询emp表的所有数据, 和对应的部门信息
由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。 
表结构: emp, dept
连接条件: emp.dept_id = dept.id
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

3、右外连接:查询右表所有数据，以及两张表交集部分数据

2). 右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

B. 查询dept表的所有数据, 和对应的员工信息(右外连接)
一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字
段。
由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查 询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

4、自连接:当前表与自身的连接查询，自连接必须使用表别名

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。
自连接的查询语法:

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

案例:
A. 查询员工 及其 所属领导的名字
表结构: emp
select a.name , b.name from emp a , emp b where a.managerid = b.id;

B. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来 
表结构: emp a , emp b
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

5、联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ... UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

案例:
A. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可。 那这里呢，我们 也可以通过union/union all来联合查询.
select * from emp where salary < 5000
union all
select * from emp where age > 50;

union all查询出来的结果，仅仅进行简单的合并，并未去重。

去重
select * from emp where salary < 5000
union
select * from emp where age > 50;

6、子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 );

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
根据子查询结果不同，分为:
A. 标量子查询(子查询结果为单个值) B. 列子查询(子查询结果为一列)
C. 行子查询(子查询结果为一行)
D. 表子查询(子查询结果为多行多列)
根据子查询位置，分为:
A. WHERE之后
B. FROM之后
C. SELECT之后

6.1标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。 常用的操作符:= <> > >= < <=

案例:
A. 查询 "销售部" 的所有员工信息  完成这个需求时，我们可以将需求分解为两步: 
1. 查询 "销售部" 部门ID
select id from dept where name = '销售部';
2. 根据 "销售部" 部门ID, 查询员工信息
3. select * from emp where dept_id = (select id from dept where name = '销售部');

B. 查询在 "方东白" 入职之后的员工信息  完成这个需求时，我们可以将需求分解为两步: 
1. 查询 方东白 的入职日期
select entrydate from emp where name = '方东白';
2. 查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白');

6.2列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。 常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL

案例:
A. 查询 "销售部" 和 "市场部" 的所有员工信息 
分解为以下两步:
 1. 查询 "销售部" 和 "市场部" 的部门ID
 select id from dept where name = '销售部' or name = '市场部';
 2. 根据部门ID, 查询员工信息
 select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

B. 查询比 财务部 所有人工资都高的员工信息
分解为以下两步:
1. 查询所有 财务部 人员工资
(1)select id from dept where name = '财务部';
(2)select salary from emp where dept_id = (select id from dept where name = '财务部');
2. 比 财务部 所有人工资都高的员工信息
  select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );

C. 查询比研发部其中任意一人工资高的员工信息
分解为以下两步:
1. 查询研发部所有人工资
 select salary from emp where dept_id = (select id from dept where name = '研发部');
2. 比研发部其中任意一人工资高的员工信息
 select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部'));

6.3行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。 常用的操作符:= 、<> 、IN 、NOT IN

案例:
A. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ; 这个需求同样可以拆解为两步进行:
1. 查询 "张无忌" 的薪资及直属领导
 select salary, managerid from emp where name = '张无忌';
2. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
 select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

6.3表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。 常用的操作符:IN

案例:
A. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息 分解为两步执行:
1. 查询 "鹿杖客" , "宋远桥" 的职位和薪资
 select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
2. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
 select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );

B. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
分解为两步执行:
3. 入职日期是 "2006-01-01" 之后的员工信息
 select * from emp where entrydate > '2006-01-01';
4. 查询这部分员工, 对应的部门信息;
 select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;