오라클 집합 연산자

1. UNION <- 두 쿼리의 결과를 모두 보여준다.  중복은 제거한다.

 1  SELECT EMPLOYEE_ID, LAST_NAME
  2  FROM EMPLOYEES
  3  WHERE LAST_NAME LIKE 'A%' OR LAST_NAME LIKE 'D%'
  4  UNION
  5  SELECT EMPLOYEE_ID, LAST_NAME
  6  FROM EMPLOYEES
  7* WHERE LAST_NAME LIKE 'D%' OR LAST_NAME LIKE 'M%'
SQL> /

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        102 De Haan
        124 Mourgos
        142 Davies
        143 Matos
        174 Abel

5 rows selected.

 

2. UNION ALL <- UNION 과 같지만 중복을 제거하지 않는다.

  1  SELECT EMPLOYEE_ID, LAST_NAME
  2  FROM EMPLOYEES
  3  WHERE LAST_NAME LIKE 'A%' OR LAST_NAME LIKE 'D%'
  4  UNION  ALL
  5  SELECT EMPLOYEE_ID, LAST_NAME
  6  FROM EMPLOYEES
  7* WHERE LAST_NAME LIKE 'D%' OR LAST_NAME LIKE 'M%'
SQL> /

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        102 De Haan
        142 Davies
        174 Abel
        102 De Haan
        124 Mourgos
        142 Davies
        143 Matos

7 rows selected.

3. INTERSECT  <- 두 쿼리 결과중에서 중복된 값만 출력한다.

  1  SELECT EMPLOYEE_ID, LAST_NAME
  2  FROM EMPLOYEES
  3  WHERE LAST_NAME LIKE 'A%' OR LAST_NAME LIKE 'D%'
  4  INTERSECT
  5  SELECT EMPLOYEE_ID, LAST_NAME
  6  FROM EMPLOYEES
  7* WHERE LAST_NAME LIKE 'D%' OR LAST_NAME LIKE 'M%'
  8  /

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        102 De Haan
        142 Davies

2 rows selected.

4. MINUS <-첫번에-두번째 형태 즉 차집합이다. 첫번재는 있지만 두번째에는 없는것만 리턴한다.

  1  SELECT EMPLOYEE_ID, LAST_NAME
  2  FROM EMPLOYEES
  3  WHERE LAST_NAME LIKE 'A%' OR LAST_NAME LIKE 'D%'
  4  MINUS
  5  SELECT EMPLOYEE_ID, LAST_NAME
  6  FROM EMPLOYEES
  7* WHERE LAST_NAME LIKE 'D%' OR LAST_NAME LIKE 'M%'
SQL> /

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        174 Abel

1 row selected.