sql大数据 基础（多表）

多表

关键词：join

1. left join

以左为尊，完整写法 (left outer join) 但是一般会把outer省略不写 ,右外连接也是一样
语法：
select 列
from A
left join B
on 条件
where
group by
having
order by
注：以A为尊 (A表的全部行都会显示,如B表没有匹配到会以null值填充)
select empno, ename, e.deptno, dname
from emp e
left join dept d
on e.deptno = d.deptno;
select *
from (select * from emp where sal<2000 ) a
left join (select * from emp where deptno = 20) b
on a.empno = b.empno ;

2. right join 以右为尊

语法:
select 列
from A
right join B
on 条件
注：以B为尊 (B表的全部行都会显示,如A表没有匹配到会以null值填充)
select *
from emp
right join dept
on emp.deptno=dept.deptno
select *
from ( select * from emp where deptno = 20) a
right join (select * from emp where sal<2000) b
on a.empno = b.empno ;

3 . inner join 内连接 (只显示都有的)

select * from
emp
inner join
dept
on emp.deptno=dept.deptno;
select *
from ( select * from emp where deptno = 20) a
inner join (select * from emp where sal<2000) b
on a.empno = b.empno ;

4. full join 全连接

A full join B
注：A,B表的全部数据都会展示
select * from emp a full join dept d on a.deptno = d.deptno;
select *
from ( select * from emp where deptno = 20) a
full join (select * from emp where sal<2000) b
on a.empno = b.empno ;

5. cross join 等同于笛卡尔积

elect * from
emp e
cross join
dept d
注：以emp表为主表 用(+) 简化语法 以 (+) 等号 对面的表为主表
select * from emp e,dept d
where e.deptno=d.deptno(+)

6. natural join 自然连接

A natural joib B
注：(自动寻找所有相同字段进行关联,去除重复列)
select * from emp natural join dept;

7. 自连接

自己和自己连接
select * from emp a left join emp b on a.mgr = b.empno;

8. 不等值连接

–查询员工的名字,工资,工资等级
select ename, sal, grade
from emp e
left join salgrade s
on e.sal between s.losal and s.hisal;
select * from salgrade;

练习：

1. 查询员工工资比改员工经理工资高的员工的信息

select *
from emp a --员工的薪水
left join emp b --经理的工资
on a.mgr = b.empno
where a.sal > b.sal;

2. 查询员工信息以及其领导的名字

select a.*, b.ename from emp a left join emp b on a.mgr = b.empno;

3)查询 员工的名字,工资, 比该员工工资高的人数

select ename, sal, (select count(*) from emp b where b.sal > a.sal)
from emp a;

4）查询各部门工资的前两名

select a.empno, a.ename, a.deptno,a.sal
from emp a
left join emp b
on a.sal < b.sal
and a.deptno = b.deptno
group by a.deptno,a.empno, a.ename,a.sal
having count(*) <2;