SQL之操作多个表

1. select ename, deptno from emp
2. union all
3. select dname, deptno from dept; //每列的数据类型必须相同

1. select deptno from emp
2. union
3. select deptno from dept;//无重复（union 与union all的区别）

1. select e.ename, d.loc from emp e, dept d where e.deptno=d.deptno;
2. select e.ename, d.loc from emp e inner join dept d on(e.deptno=d.deptno);

1. create table v as (select ename, job from emp where job='clerk');
2. select e.ename, e.job, e.sal from emp e, v where e.ename=v.ename and e.job=v.job;
3. select e.ename, e.job, e.sal from emp e inner join v on(e.ename=v.ename and e.job=v.job);
4. select ename, job, sal from emp where(ename,job) in (select ename, job from v);

1. select deptno from dept where deptno not in (select deptno from emp);
2. //当(select deptno from emp)中出现null值是会出现错误
3. select d.deptno from dept d 
4. where not exists (select null from emp e where d.deptno=e.deptno);
5. select d.* from dept d left outer join emp e on(d.deptno=e.deptno) where e.deptno is null; 
6. select d.* from dept d where d.deptno not in(select deptno from emp);

1. select e.ename, d.loc, eb.received from 
2. emp e join dept d on (e.deptno=d.deptno)
3. left join emp_bonus eb on(e.empno=eb.empno)
4. order by d.loc; //内联加外联

1. select e.ename, d.loc, 
2. (select eb.received from emp_bonus eb where eb.empno=e.empno) as r
3. from emp e, dept d where e.deptno=d.deptno
4. order by 2; //使用标量子查询解决外联的性能问题

1. create view v as 
2. select ename,job from emp where deptno!=10
3. union all
4. select ename,job from emp where ename='ward';

1. select * from (select e.ename,e.job,count(*) as cnt from emp e group by ename,job) e where not exists(select null from (select v.ename,v.job,count(*) as cnt from v group by ename,job) v where e.ename=v.ename and e.job=v.job and e.cnt=v.cnt )
2. union all
3. select * from(select v.ename,v.job,count(*) as cnt from v group by ename,job) v where not exists( select null from(select e.ename,e.job,count(*) as cnt from emp e group by ename,job)e where v.ename=v.ename and v.job=e.job and v.cnt=e.cnt);
4. 
   

1. select e.empno,e.ename,e.sal* 
   case 
   when eb.type=1 then 0.1 
   when eb.type=2 then 0.2
   when eb.type=3 then 0.3
   else 0
   end as bonus
   from emp e, emp_bonus eb 
   where e.empno=eb.empno;
   

1. select empno, ename, sum(bonus) as total_bonus from 
   (select e.empno,e.ename,e.sal* 
   case 
   when eb.type=1 then 0.1 
   when eb.type=2 then 0.2
   when eb.type=3 then 0.3
   else 0
   end as bonus
   from emp e, emp_bonus eb 
   where e.empno=eb.empno)
   group by empno, ename;