查找只存在于一个表中的记录
select deptno
from dept
except
select deptno
from emp
从一个表中检索出与另一个表不相关的行
-- 哪些部门没有员工？
select d.*
from dept d left join emp e on d.deptno = e.deptno
where e.deptno is null
新增连接查询而不影响其他连接查询
-- 查找所有员工的姓名、部门所在地和奖金
select 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 loc
确定两个表中是否存在相同的记录
-- 不仅要找出不同的记录，还要找出重复的记录
（seleect empno, ename, job, mgr, hiredate, sal, comm, deptno,
count(*) as cnt from V
group by empno, ename, job, mgr, hiredate, sal, comm, deptno
except
select empno, ename, job, mgr, hiredate, sal, comm, deptno,
count(*) as cnt
from emp
group by empno, ename, job, mgr, hiredate, sal, comm, deptno
)
union all
(seleect empno, ename, job, mgr, hiredate, sal, comm, deptno,
count(*) as cnt from emp
group by empno, ename, job, mgr, hiredate, sal, comm, deptno
except
select empno, ename, job, mgr, hiredate, sal, comm, deptno,
count(*) as cnt
from V
group by empno, ename, job, mgr, hiredate, sal, comm, deptno
)
识别并消除笛卡尔积
-- 错误
select e.name, d.loc
from emp e, dept d
where e.deptno = 10
-- 正确
select e.name, d.loc
from emp e, dept d
where e.deptno = d.deptno and e.deptno = 10
组合使用连接查询和聚合函数
-- 使用distinct计算工资总额
select deptno,
sum(distinct sal) as total_sal,
sum(bonus) as total_bonus,
from (
select e.empno, e.ename, e.sal, e.deptno,
e.sal * case when eb.type = 1 then .1
when eb.type = 2 then.05
else .3 end as bonus
from emp e, emp_bonus eb
where e.empno = eb.empno and e.deptno = 10
) x
group by deptno
组合使用外连接查询和聚合函数
select deptno,
sum(distinct sal) as total_sal,
sum(bonus) as total_bonus,
from (
select e.empno, e.ename, e.sal, e.deptno,
e.sal * case when eb.type = 1 then .1
when eb.type = 2 then.05
else .3 end as bonus
from emp e left join emp_bonus eb
on e.empno = eb.empno and e.deptno = 10
) x
group by deptno
从多个表中返回缺失值
-- 全外连接(full outer join),基于一个共同值，从两个表中返回缺失值
select d.deptno,d.dname, e.ename
from emp e full outer join dept d
on e.deptno = d.deptno
