Code Word SQL (6)

  
 

   
  

  
--外连接（用(+)书写）
   
  

  
--查询出所有部门的雇员信息
   
  

  
select b.deptno, b.dname, a.* from emp a, dept b where a.deptno(+)=b.deptno ;
   
  

  
--查询出所有部门的雇员数
   
  

  
select b.deptno, count(*) from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno;
   
  

  

   
  
select   b   deptno    count   ( empno     from   emp a  ,  Span class= "PLN" > Dept b  where   a   deptno   (+) =  b   deptno  group    by   b   deptno     -- count   for non-empty fields, Pay special attention to  
   
  

  
--查询出所有部门的职位为经理的雇员数
   
  
Selectb.Deptno,Count(empno)  fromEMP a,Dept Bwherea.Deptno(+)=b.Deptno anda.Job(+)=' MANAGER ' Group  byb.Deptno;
   
  

  
--查询出没有雇员的部门
   
  

  
select b.* from emp a, dept b where a.deptno(+)=b.deptno and a.empno is null;
  
 

It's worth mentioning that Oracle 9i

From for notes (Wiz)

Code Word SQL (6)