多表连接查询
create table t1(key1 char,v1 int);

create table t2(key2 char,v2 int);

insert into t1 values('a',1),('a',2),('b',3),('c',4),('a',13);

insert into t2 values('b',10),('b',11),('a',12),('a',13),('e',14);

select * from t1;
select * from t2;

1. 内连接(满足条件就连接显示)（其他数据库还有全连接：full join）
-- select 字段1[,…] from 表1 [inner] join 表2 on 表1.key=表2.key;
select *
from t1
join t2
on t1.key1=t2.key2;

select *
from emp
join dept
on emp.deptno=dept.deptno;

2. 左连接(以左边表为主去连接，左边的都会显示，右边没有对应的不显示)
-- select 字段1[,…] from 表1(主表) left join 表2 on 表1.key=表2.key;
select *
from t1
left join t2
on t1.key1=t2.key2;

select *
from emp
left join dept
on emp.deptno=dept.deptno;

3.右连接(以右边表为主去连接，右边的都会显示，左边没有对应的不显示)
-- select 字段1[,…] from 表1 right join 表2(主表) on 表1.key=表2.key;
select *
from t1
right join t2
on t1.key1=t2.key2;

select *
from emp
right join dept
on emp.deptno=dept.deptno;

4. 合并查询（联合查询）:被合并的结果集的列数、顺序和数据类型必须完全一致

-- union去重:select 字段1[,字段2,…] from 表名 union select 字段1[,字段2,…] from 表名;
select * from t1 union select * from t2;

5. union all不去重:select 字段1[,字段2,…] from 表名 union all select 字段1[,字段2,…] from 表名;
select * from t1 union all select * from t2;

多表查询练习
-- 创建salgrade表
create table salgrade(grade int,losal int,hisal int);
insert into salgrade values(1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

select * from emp;
select * from dept;
select * from salgrade;


6. 查询每位员工的ename,dname,sal
select e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno=d.deptno;

select ename,sal,dname
from emp
left join dept
on emp.deptno=dept.deptno;

7. 查询各地区的员工数（统计每个地区，没有员工计为0）
select loc,count(empno)
from dept
left join emp
on emp.deptno=dept.deptno
group by loc;

8. 查询manager的姓名、所属部门名称和入职日期：ename,dname,job,hiredate(内连接/笛卡尔积连接)
select ename,job,hiredate,dname
from dept join emp on emp.deptno=dept.deptno
where job='manager'; -- 内连接查询(好)

select ename,job,hiredate,dname,emp.deptno
from emp,dept
where emp.deptno=dept.deptno and job='manager'; -- -- 笛卡尔积连接查询

9.查询每位员工的工资等级；empno,ename,sal,grade(不等值连接)
select empno,ename,sal,grade
from emp,salgrade
where sal between losal and hisal
order by grade asc; -- 笛卡尔积连接查询

select empno,ename,sal,grade
from emp left join salgrade
on sal between losal and hisal
order by grade asc; -- 左连接查询


10.查询所有员工姓名及其直属领导姓名（自连接：通过别名，将同一张表视为多张表）(e1：员工表，e2：领导表)
select e1.ename 员工姓名,e2.ename 领导姓名
from emp e1 left join emp e2 on e1.mgr=e2.empno;
-- select e1.ename 员工姓名,e2.ename 领导姓名 from emp e1,emp e2 where e1.mgr=e2.empno;(笛卡尔积会忽略mgr为空的员工)

11. 查询入职日期早于其直属领导的员工：empno,ename,dname (e1：员工表，e2：领导表)
select e1.empno,e1.ename,dname
from emp e1 left join emp e2 on e1.mgr=e2.empno
left join dept on e1.deptno=dept.deptno
where e1.hiredate<e2.hiredate;