Oracle 多表查询

1.  SQL语句的执行顺序   

SELECT DNAME, MAX(SAL) -- 5 对列筛选（分组字段或聚集函数）    

  FROM EMP   

  LEFT OUTER JOIN DEPT -- 1 确定表    

 USING (DEPTNO)   

 WHERE DEPTNO > 0 -- 2 确定行（记录）    

 GROUP BY DNAME -- 3 将行分组      

HAVING MAX(COMM) IS NULL OR MAX(COMM) > 0 -- 4 对组筛选      

 ORDER BY DNAME -- 6 对结果集排序   

2.  等值查询:    

在存在主外键关系的表中，使用等号建立表之间的连接   

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME   

     FROM EMP A, DEPT B   

    WHERE A.DEPTNO = B.DEPTNO;   

  

   SQL99新标准:内连接(inner join)   

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME   

     FROM EMP A   

    INNER JOIN DEPT B -- inner可省略   

       ON A.DEPTNO = B.DEPTNO;   

3.  非等值查询:    

使用非等号关系运算符进行多表的连接    

   SELECT A.ENAME, A.SAL, B.GRADE   

     FROM EMP A, SALGRADE B   

    WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL;   

4.  外连接(outer join):    

类似等值查询，使用特定的符号(+)   

   (+)符号如果出现在where子句等号的左边，叫左外连接，左外连接会显示右表的全部数据；   

   (+)符号如果出现在where子句等号的右边，叫右外连接，右外连接会显示左表的全部数据；   

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME   

     FROM EMP A, DEPT B   

    WHERE A.DEPTNO(+) = B.DEPTNO;   

      

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME   

     FROM EMP A, DEPT B   

    WHERE A.DEPTNO = B.DEPTNO(+);   

      

   SQL99新标准：   

   左外连接：   

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME   

     FROM EMP A   

     LEFT OUTER JOIN DEPT B -- outer可省略   

       ON A.DEPTNO = B.DEPTNO;   

  

   右外连接：   

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME   

     FROM EMP A   

    RIGHT OUTER JOIN DEPT B -- outer可省略   

       ON A.DEPTNO = B.DEPTNO;   

  

   全外连接:外连接是在等值(内)连接的基础上将左表和右表的未匹配数据都加上   

   SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME   

     FROM EMP A   

     FULL OUTER JOIN DEPT B -- outer可省略   

       ON A.DEPTNO = B.DEPTNO;   

5.  自连接:   

特殊的多表连接，连接的多个表来自于同一个表   

   SELECT A.EMPNO, A.ENAME, B.ENAME MANAGER   

     FROM EMP A, EMP B   

    WHERE A.MGR = B.EMPNO;   

6.  自然连接(natural join):   

基于多个表有一个或多个相同字段名，且这些字段的类型相同   

   由oracle自行决定哪些列作为连接的条件:将不同表中的那些具有相同名称和数据类型的字段用等值连接起来。   

   SELECT A.ENAME, B.DNAME FROM EMP A NATURAL JOIN DEPT B;   

7.  交叉查询(cross join): 笛卡尔集   

   SELECT A.ENAME, A.SAL, B.GRADE FROM EMP A, SALGRADE B;   

8.  join...using:    

   使用 USING 可以在natural join时有多个列满足条件，指定具体的字段做等值连接   

   如果列名相同但是数据类型不同，这时不能使用natural join 可以使用 using来连接。   

   当多列匹配时，using只能匹配一列。   

   在引用的列前面不能有表名或者表别名。   

   natural join和using 是相互排斥的   

      

   SELECT A.ENAME, B.DNAME FROM MYEMP A JOIN DEPT B USING (DEPTNO);   

9.  union[all]:    

取多个查询结果的并集,不要all表示重复的记录只保留一条   

   注意：union、minus、intersect做连接查询时，多个查询结果的字段和类型要一致   

      

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO   

     FROM EMP   

    WHERE JOB = 'CLERK'  

   UNION ALL -- ALL可以省略   

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO   

     FROM EMP   

    WHERE DEPTNO = 20   

    ORDER BY ENAME;   

10.  minus: 取多个查询结果的差集   

   注意：union、minus、intersect做连接查询时，多个查询结果的字段和类型要一致   

      

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO   

     FROM EMP   

    WHERE JOB = 'CLERK'  

   MINUS   

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO   

     FROM EMP   

    WHERE DEPTNO = 20   

    ORDER BY EMPNO;   

      

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO   

     FROM EMP   

    WHERE DEPTNO = 20   

   MINUS   

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO   

     FROM EMP   

    WHERE JOB = 'CLERK'  

    ORDER BY EMPNO;   

11.  intersect: 取多个查询结果的交集   

   注意：union、minus、intersect做连接查询时，多个查询结果的字段和类型要一致   

      

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO   

     FROM EMP   

    WHERE JOB = 'CLERK'  

   INTERSECT  

   SELECT EMPNO, ENAME, JOB, SAL, DEPTNO   

     FROM EMP   

    WHERE DEPTNO = 20   

    ORDER BY EMPNO;