子查询与关联查询
1、子查询
1.1、单行子查询
select * from emp
where sal = (select sal from emp where empno = 7876);
1.2、多行子查询
多行子查询
any:
select * from emp where sal > any(select avg(sal) from emp group by deptno);
注:any(collection) 函数. 只要大于collection中任意一个即满足>条件。与in用法类似
all:
select * from emp where sal > all(select avg(sal) from emp group by deptno);
注:all(collection)函数,比子查询返回结果中的所有值都大才满足>条件。
in:
select * from emp where job in (select job from emp where ename = 'Smith' or ename = 'Halen');
2、关联查询
2.1、内连接
- 只选取符合连接条件的记录
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
2.2、左外连接
- 以左边表为主(左边表的记录都保留),右边表有的,左边表没有的不显示
SELECT e.ename, d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;
2.3、右外连接
- 以右边表为主(右边表的记录都保留),左边表有的,右边表没有的不显示
SELECT e.ename, d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;
2.5、全外连接
- 保留所有字段
SELECT e.ename, d.dname
FROM emp e FULL OUTER JOIN dept d
ON e.deptno = d.deptno;
2.6、自连接
- 关联的字段都在一张表,代表的例子就是多级部门表
SELECT worker.empnow_empno, worker.enamew_ename, manager.empnom_empno, manager.enamem_ename
FROM emp worker join emp manager
ON worker.mgr = manager.empno;