Oracle连接查询

1、NVL(comm,0)

    NVL函数将NULL 变为指定的内容。

2、DECODE(col/expression,search1,result1,search2,result2,[…])

     select decode(1,1,'内容是1',2,'内容是2',3,'内容是3') from dual;

3、 自然连接不显示含有NULL的值

 SELECT E.ENAME, E.SAL, D.DNAME, E.DEPTNO
   FROM EMP E, DEPT D
  WHERE E.DEPTNO = D.DEPTNO
  ORDER BY ENAME;
等同于
SELECT * FROM dept NATURAL JOIN emp;
SELECT * FROM emp join dept using(deptno);

4、左连接（+）在右面，以左边的表为准，显示左边含有NULL的数据

SELECT E.ENAME, E.SAL, D.DNAME, E.DEPTNO
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO(+)
 ORDER BY ENAME;
等同于

 SELECT E.ENAME, E.SAL, D.DNAME, E.DEPTNO
  FROM EMP E LEFT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO ORDER BY ename;

 

 

5、右连接（+）在左面，以右边的表为准，显示右边含有NULL的数据

 SELECT E.ENAME, E.SAL, D.DNAME, E.DEPTNO
   FROM EMP E, DEPT D
  WHERE E.DEPTNO(+) = D.DEPTNO
  ORDER BY ENAME;
等同于
 SELECT E.ENAME, E.SAL, D.DNAME, E.DEPTNO
  FROM EMP E RIGHT OUTER JOIN DEPT D
 ON E.DEPTNO = D.DEPTNO
 ORDER BY ENAME;

6、全连接
 SELECT E.ENAME, E.SAL, D.DNAME, E.DEPTNO
  FROM EMP E FULL OUTER JOIN DEPT D
 ON E.DEPTNO = D.DEPTNO
 ORDER BY ENAME;

7、交叉连接
 SELECT * FROM t1 CROSS JOIN t2;

8、查询员工的工资等级

SELECT E.ENAME, S.GRADE
  FROM EMP E, SALGRADE S
 WHERE S.LOSAL <= E.SAL
   AND E.SAL <= S.HISAL;
  
9、exists查询

查询没有员工的部门

SELECT * FROM dept d WHERE NOT EXISTS (SELECT * FROM emp e WHERE e.deptno=d.deptno);

 

10、自连接查询
查询员工的上级名称

11、嵌套查询

SELECT * FROM EMP WHERE (deptno,job)=(SELECT deptno, job FROM emp WHERE ename='SMITH');

UPDATE emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';