关键词：join
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 ;
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 ;
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 ;
cross join 等同于笛卡尔积 elect * from emp e cross join dept d 注：以emp表为主表 用(+) 简化语法 以 (+) 等号 对面的表为主表 select * from emp e,dept d where e.deptno=d.deptno(+)
natural join 自然连接 A natural joib B 注：(自动寻找所有相同字段进行关联,去除重复列) select * from emp natural join dept;
自连接 自己和自己连接 select * from emp a left join emp b on a.mgr = b.empno;
不等值连接 –查询员工的名字,工资,工资等级 select ename, sal, grade from emp e left join salgrade s on e.sal between s.losal and s.hisal; select * from salgrade;
练习：
查询员工工资比改员工经理工资高的员工的信息 select * from emp a --员工的薪水 left join emp b --经理的工资 on a.mgr = b.empno where a.sal > b.sal;
查询员工信息以及其领导的名字 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;
