create view V
as
select ename,job,sal
from emp
where job = 'CLERK'
select * from V
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300
2.4.2. sql
select e.empno,e.ename,e.job,e.sal,e.deptno
from emp e join V
on ( e.ename = v.ename
and e.job = v.job
and e.sal = v.sal )
2.4.3. sql
select empno,ename,job,sal,deptno
from emp
where (ename,job,sal) in (
select ename,job,sal from emp
intersect
select ename,job,sal from V
)
3. 查找只存在于一个表中的数据
3.1. MySQL
3.1.1. sql
select deptno
from dept
where deptno not in (select deptno from emp)
3.1.2. sql
select distinct deptno
from dept
where deptno not in (select deptno from emp)
3.1.2.1. 排除重复项
3.1.3. 在使用NOT IN时,要注意Null值
3.2. PostgreSQL
3.2.1. sql
select deptno from dept
except
select deptno from emp
3.3. Oracle
3.3.1. sql
select deptno from dept
minus
select deptno from emp
3.4. 要点
3.4.1. 参与运算的两个SELECT列表要有相同的数据类型和值个数
3.4.2. 不返回重复项
3.4.3. Null值不会产生问题
3.5. sql
select deptno
from dept
where deptno not in ( 10,50,null )
( no rows )
select deptno
from dept
where not (deptno=10 or deptno=50 or deptno=null)
( no rows )
3.5.1. 三值逻辑
3.6. 免受Null值影响的替代方案
3.6.1. sql
select d.deptno
from dept d
where not exists ( select null
from emp e
where d.deptno = e.deptno )
4. 从一个表检索与另一个表不相关的行
4.1. 使用外连接并过滤掉Null值
4.2. sql
select d.*
from dept d left outer join emp e
on (d.deptno = e.deptno)
where e.deptno is null
4.2.1. 反连接(anti-join)
5. 新增连接查询而不影响其他连接查询
5.1. 外连接既能够获得额外信息,又不会丢失原有的信息
5.1.1. sql
select e.ename, d.loc, eb.received
from emp e join dept d
on (e.deptno=d.deptno)
left join emp_bonus eb
on (e.empno=eb.empno)
order by 2
5.2. 使用标量子查询
5.2.1. 把子查询放置在SELECT列表里
5.2.2. 在不破坏当前结果集的情况下,标量子查询是为现有查询语句添加额外数据的好办法
5.2.3. sql
select e.ename, d.loc,
(select eb.received from emp_bonus eb
where eb.empno=e.empno) as received
from emp e, dept d
where e.deptno=d.deptno
order by 2
select d.deptno,d.dname,e.ename
from dept d full outer join emp e
on (d.deptno=e.deptno)B
8.2. 合并两个外连接的查询结果
8.2.1. sql
select d.deptno,d.dname,e.ename
from dept d right outer join emp e
on (d.deptno=e.deptno)
union
select d.deptno,d.dname,e.ename
from dept d left outer join emp e
on (d.deptno=e.deptno)