【SQL】表連線 --半連線

 半連線： 當一張表在另一張表找到匹配的記錄之後，半連線（semi-jion）返回第一張表中的記錄。與條件連線相反，即使在右節點中找到幾條匹配的記錄，左節點的表也只會返回一條記錄。另外，右節點的表一條記錄也不會返回。半連線通常使用IN  或 EXISTS 作為連線條件。下面是一個例子：

SQL> set linesize 999
SQL>  select d.deptno,d.dname,d.loc
  2  from scott.dept d
  3  where d.deptno IN  (select e.deptno from scott.emp e);

Execution Plan
----------------------------------------------------------
Plan hash value: 2365756639

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     3 |    69 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |         |     3 |    69 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        750  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

-------------------------------------   EXISTS  -----------------

SQL> select d.deptno,d.dname,d.loc
  2  from scott.dept d
  3  where EXISTS (select e.deptno from scott.emp e WHERE e.deptno = d.deptno);

Execution Plan
----------------------------------------------------------
Plan hash value: 2365756639

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     3 |    69 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |         |     3 |    69 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        750  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

附上半連線與條件連線的結果：

SQL> select d.deptno,d.dname,d.loc//半連線
  2  from scott.dept d
  3   where EXISTS (select e.deptno from scott.emp e WHERE e.deptno = d.deptno);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO

SQL> select e.ename ,s.grade//條件連線
  2  from emp e,salgrade s
  3  where e.sal between s.losal and s.hisal;

ENAME           GRADE
---------- ----------
SMITH               1
JAMES               1
ADAMS               1
WARD                2
MARTIN              2
MILLER              2
TURNER              3
ALLEN               3
CLARK               4
BLAKE               4
JONES               4

ENAME           GRADE
---------- ----------
SCOTT               4
FORD                4
KING                5

14 rows selected.

可以看到 grade 有很多的重複值。