SQL多表联合查询 (内、外关联查询)

      多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候,需要显示的数据来自多张表。

 

 交叉连接查询[产生笛卡尔积]

          语法：select * from A，B

•   内连接查询（使用的关键字inner join --inner可以省略）

    隐式内连接（SQL92标准）：select * from A，B where 条件；

    显示内连接（SQL99标准）：select * from A inner join B on 条件；

•   外连接查询（使用的关键字 outer join --outer可以省略）

    左外连接：left outer join   「  select * from A left outer join B on 条件; 」

    右外连接：right outer join   「 select * from A right outer join B on 条件; 」

    满外连接：full outer join   「  select * from A full outer join B on 条件; 」

•   子查询     select的嵌套

•   表自关联：  将一张表当成多张表来用

准备查询数据：接下来准备多表查询需要数据，注意，外键约束对多表查询并无影响。

-- 创建部门表
create table if not exists dept3(
 depton varchar(20) primary key, -- 部分号
 name varchar(20) -- 部门名字
);

-- 创建员工表
create table if not exists emp3(
eid varchar(20) primary key, --员工编号
ename varchar(20), --员工名字
age int,-- 员工年龄
dept_id varchar(20) --员工所属部门
)

-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');

-- 给emp表添加数据
insert into emp3 values('1','张三',20,'1001');
insert into emp3 values('2','李四',21,'1001');
insert into emp3 values('5','王五',85,'1002');
insert into emp3 values('6','赵六',33,'1002');
insert into emp3 values('8','甲乙',60,'1003');
insert into emp3 values('9','丙丁',58,'1003');
insert into emp3 values('10','戊戌',71,'1005');

 

交叉连接查询：

• 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积。

• 笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配。

• 假如A表有m行数据，B表有n行数据，则返回m*n行数据。

• 笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选。

• 格式：   select * from 表1，表2，表3...;

• 实现：

-- 交叉连接查询
select * from dept3,emp3;

 

内连接查询：内连接查询求多张表的交集。

       操作：

-- 内连接查询
/*
隐式内连接（SQL92标准）:select * from A,B where 条件;
显示内连接（SQL99标准）：select *from A inner join B on 条件；
*/
-- 查询每个部门的所属员工
select * from dept3,emp3 where deptno=dept_id;
-- 隐式内连接
select * from dept3,emp3 where dept3.deptno=emp3.dept_id;
select * from dept3 a,emp3 b where a.deptno=b.dept_id;
-- 显示内连接
select * from dept3 inner join emp3 on dept3.deptno=emp3.dept_id;
select * from dept3 a join emp3 b on a.deptno=b.dept_id;


-- 查询研发部门的所属员工
select * from dept3,emp3 where dept3.deptno=emp3.dept_id;
-- 隐式内连接
select * from dept3 a,emp3 b where a.depton=b.dept_id and name='研发部';
-- 显示内连接
select * from dept3 a join emp3 on a.depton=b.dept_id and name='研发部';

-- 查询研发部和销售部的所属员工
select * from dept3 a join emp3 b on a.depton=b.dept_id and (name='研发部' or name='销售部');
select * from dept3 a join emp3 b on a.depton=b.dept_id and name in('研发部','销售部');

-- 查询每个部门的员工数，并升序排序

select a.depton,count(1) from dept3 a join emp3 b on a.depton=b.dept_id group by a.deptno;

-- 查询每个部门的员工数，并升序排序

select a.name,a.deptno,count(1) from dept3 a join emp3 on a.deptno=b.dept_id group by a.deptno,name;

-- 查询人数大于等于3的部门，并按照人数降序排序
select 
a.deptno,
a.name,
count(1) as total_cnt
from dept3 a
join emp3 b on a.deptno=b.dept_id
group by
a.deptno,a.name
having total_cnt>=3
order by
total_cnt desc;

 

 外连接查询

        外连接分为左外连接（left outer join），右外连接（right outer join），满外连接（full outer join）。注意：oracle里面有full join，可是在my sql对full join支持的不好。我们可以使用union来达到目的。

• 格式：

  左外连接：left outer join  「select * from A left outer join B on 条件；」

  右外连接：right outer join  「select * from A right outer join B on 条件；」

  满外连接： full outer join  「select *from A full outer join B on 条件；」

• 操作：

-- 外连接查询
-- 查询哪些部门有员工，哪些部门没有员工
user mydb3
select * from dept3 left outer emp3 on dept3.deptno=emp3.dept_id;

-- 查询员工有对应的部分，哪些没有
select * from dept3 right outer join emp3 on dept3.depton=emp3.dept_id;

-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno=emp3.dept_id
union
select * from dept3 right outer join emp3 on dept3.depton=emp3.dept_id;