In,内链接和空值

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            1100
ADAMS      CLERK            1100
JAMES      CLERK             950
JAMES      CLERK             950
MILLER     CLERK            1300
MILLER     CLERK            1300
SMITH      CLERK             800
SMITH      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         10

SQL> 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         10


IN 有空值的情况呢？

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            1100
MILLER     CLERK            1300
JAMES      CLERK
SMITH      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