Code Word SQL (5)

  
 

   
  

  
--等值连接
   
  

  
select   dname    loc    empno    ename  from   emp a    Dept b  where   a   deptno  =  b   deptno  and   b   deptno  =  20    
   
  

  
--自连接
   
  
Selecta.empno,a.Sal,b.empno,b.ename,b.Sal fromEMP a,EMP Bwherea.Mgr=b.empno anda.ename=' SCOTT ';
   
  

  
--笛卡尔积
   
  
Selecta||' + '||b||' = '|| (a+b)  as "3*3 addition table"  from (SelectRowNum a fromall_objectswhererownum<4), (Selectrownum b fromall_objectswhererownum<4);
   
  

  
--内连接
   
  
select   empno    ename    sal    Salgrade   grade  from   EMP    Salgrade  where   EMP   deptno  =  10    and   emp   sal between Salgrade   losal  and   Salgrade   hisal   
   
  

  
select   empno    ename    Sal    Salgrade   grade  from   emp inner JOIN Salgrade on EMP   deptno  =  10    and   emp   sal between Salgrade   losal  and   Salgrade   hisal    
   
  

  
- -Anti-connection   
   
  

  
select * from emp where deptno not in (select deptno from dept where loc in(‘NEW YORK‘, ‘DALLAS‘));
   
  

  

   
  

  
select * from dept where deptno not in (select deptno from emp);
   
  

  

   
  

  
--半连接
   
  

  
select    *    from   emp a  where   exists   ( select    1  Span class= "PLN" > from   Dept b  where   loc  =   ' NEW YORK '    and   a   deptno  =  b   deptno    
   
  

  
select a.* from emp a, dept b where loc=‘NEW YORK‘ and a.deptno=b.deptno;
   
  

  

   
  

  
select    *    from   dept a  where   exists   ( select    1  Span class= "PLN" > from   EMP b  where   a   deptno  =  b   deptno  and   b   sal  >  2900    
   
  

  
select   a  .*    from   dept a    EMP b    a   deptno  =  b   deptno  and   b   sal  >  2900    
   
  

  
select distinct a.* from dept a , emp b where a.deptno=b.deptno and b.sal>2900;
  
 

From for notes (Wiz)

Code Word SQL (5)