In,内链接和空值

来源：互联网发布：浙江省网络监管服务网编辑：程序博客网时间：2024/06/13 05:21
SQL> select * from emp;     EMPNO ENAME      JOB               MGR HIREDATE              SAL       COMM     DEPTNO---------- ---------- ---------- ---------- -------------- ---------- ---------- ----------      7369 SMITH      CLERK            7902 17-12月-80            800                    20      7499 ALLEN      SALESMAN         7698 20-2月 -81           1600        300         30      7521 WARD       SALESMAN         7698 22-2月 -81           1250        500         30      7566 JONES      MANAGER          7839 02-4月 -81           2975                    20      7654 MARTIN     SALESMAN         7698 28-9月 -81           1250       1400         30      7698 BLAKE      MANAGER          7839 01-5月 -81           2850                    30      7782 CLARK      MANAGER          7839 09-6月 -81           2450                    10      7788 SCOTT      ANALYST          7566 09-12月-82           3000                    20      7839 KING       PRESIDENT             17-11月-81           5000                    10      7844 TURNER     SALESMAN         7698 08-9月 -81           1500          0         30      7876 ADAMS      CLERK            7788 12-1月 -83           1100                    20      7900 JAMES      CLERK            7698 03-12月-81            950                    30      7902 FORD       ANALYST          7566 03-12月-81           3000                    20      7934 MILLER     CLERK            7782 23-1月 -82           1300                    10已选择14行。SQL>  select * from emp2 order by 1;ENAME      JOB               SAL---------- ---------- ----------ADAMS      CLERK            1100ADAMS      CLERK            1100JAMES      CLERK             950JAMES      CLERK             950MILLER     CLERK            1300MILLER     CLERK            1300SMITH      CLERK             800SMITH      CLERK             800已选择8行。SQL> SELECT empno, ename, job, sal, deptno  2        FROM emp  3       WHERE (ename, job, sal) IN (SELECT ename, job, sal FROM emp2);     EMPNO ENAME      JOB               SAL     DEPTNO---------- ---------- ---------- ---------- ----------      7369 SMITH      CLERK             800         20      7876 ADAMS      CLERK            1100         20      7900 JAMES      CLERK             950         30      7934 MILLER     CLERK            1300         10SQL> SELECT a.empno, a.ename, a.job, a.sal, a.deptno  2    FROM emp a  3   INNER JOIN emp2 b ON (b.ename = a.ename AND b.job = a.job AND  4                        b.sal = a.sal);     EMPNO ENAME      JOB               SAL     DEPTNO---------- ---------- ---------- ---------- ----------      7369 SMITH      CLERK             800         20      7369 SMITH      CLERK             800         20      7876 ADAMS      CLERK            1100         20      7876 ADAMS      CLERK            1100         20      7900 JAMES      CLERK             950         30      7900 JAMES      CLERK             950         30      7934 MILLER     CLERK            1300         10      7934 MILLER     CLERK            1300         10已选择8行。由于EMP2表有重复数据,导致半连接的写法数据翻倍,得去除重复数据SQL> SELECT distinct a.empno, a.ename, a.job, a.sal, a.deptno  2    FROM emp a  3   INNER JOIN emp2 b ON (b.ename = a.ename AND b.job = a.job AND  4                        b.sal = a.sal);     EMPNO ENAME      JOB               SAL     DEPTNO---------- ---------- ---------- ---------- ----------      7900 JAMES      CLERK             950         30      7876 ADAMS      CLERK            1100         20      7369 SMITH      CLERK             800         20      7934 MILLER     CLERK            1300         10IN 有空值的情况呢？SQL> select * from emp2 order by 6 nulls last;     EMPNO ENAME      JOB               MGR HIREDATE              SAL       COMM     DEPTNO---------- ---------- ---------- ---------- -------------- ---------- ---------- ----------      7876 ADAMS      CLERK            7788 12-1月 -83           1100                    20      7521 WARD       SALESMAN         7698 22-2月 -81           1250        500         30      7654 MARTIN     SALESMAN         7698 28-9月 -81           1250       1400         30      7934 MILLER     CLERK            7782 23-1月 -82           1300                    10      7844 TURNER     SALESMAN         7698 08-9月 -81           1500          0         30      7499 ALLEN      SALESMAN         7698 20-2月 -81           1600        300         30      7782 CLARK      MANAGER          7839 09-6月 -81           2450                    10      7698 BLAKE      MANAGER          7839 01-5月 -81           2850                    30      7566 JONES      MANAGER          7839 02-4月 -81           2975                    20      7788 SCOTT      ANALYST          7566 09-12月-82           3000                    20      7902 FORD       ANALYST          7566 03-12月-81           3000                    20      7839 KING       PRESIDENT             17-11月-81           5000                    10      7369 SMITH      CLERK            7902 17-12月-80                                   20      7900 JAMES      CLERK            7698 03-12月-81                                   30已选择14行。SQL> select *  from emp3 order by 3 nulls last;ENAME      JOB               SAL---------- ---------- ----------ADAMS      CLERK            1100MILLER     CLERK            1300JAMES      CLERKSMITH      CLERK此时SQL>              SELECT empno, ename, job, sal, deptno  2            FROM emp2  3           WHERE (ename, job, sal) IN (SELECT ename, job, sal FROM emp3);     EMPNO ENAME      JOB               SAL     DEPTNO---------- ---------- ---------- ---------- ----------      7876 ADAMS      CLERK            1100         20      7934 MILLER     CLERK            1300         10会丢失 SMITH和JAMES 2条记录,改写为:SQL> SELECT empno, ename, job, sal, deptno  2    FROM emp2  3   WHERE (ename || job || sal) IN (SELECT ename || job || sal FROM emp3);     EMPNO ENAME      JOB               SAL     DEPTNO---------- ---------- ---------- ---------- ----------      7369 SMITH      CLERK                         20      7876 ADAMS      CLERK            1100         20      7900 JAMES      CLERK                         30      7934 MILLER     CLERK            1300         10
0 0