'테크'에 해당되는 글 1건

  1. 2015.11.17 [이포넷 IT 이야기] [SQL] UNION, INTERSECT, MINUS Operator
대부분 아는 것이지만 너무 쉬워서 간혹 의도한 바와 다른 결과로 치명적인 데이터 오류를 발생하거나 
쉽게 처리할 수 있는 쿼리를 난해한 서브쿼리로 어렵게 구현하는 경우에 잘 사용하면 좋습니다.

- UNION ALL
모든 병합 데이터(중복 허용)를 출력
예)
Select 'a' As v From dual
Union All
Select 'b' From dual
Union All
Select 'b' From dual

결과)
V
--
a
b
b

- UNION
병합 데이터 중 중복 배제 출력
예)
Select v From (
  Select 'a' As v From dual
  Union All
  Select 'b' From dual
  Union All
  Select 'b' From dual
)
Union
Select v From (
  Select 'b' As v From dual
  Union All
  Select 'b' From dual
  Union All
  Select 'c' From dual
)

결과)
V
--
a
b
c

- INTERSECT
병합 데이터 중 양쪽 존재, 중복 배제 출력
예)
Select v From (
  Select 'a' As v From dual
  Union All
  Select 'b' From dual
  Union All
  Select 'b' From dual
)
Intersect
Select v From (
  Select 'b' As v From dual
  Union All
  Select 'b' From dual
  Union All
  Select 'c' From dual
)

결과)
V
--
b

- MINUS
병합 데이터 중 첫째 쿼리에서 만 존재, 중복 배제 출력
예)
Select v From (
  Select 'a' As v From dual
  Union All
  Select 'a' From dual
  Union All
  Select 'b' From dual
)
Minus
Select v From (
  Select 'b' As v From dual
  Union All
  Select 'b' From dual
  Union All
  Select 'c' From dual
)

결과)
V
--
a

- 복합사용
Select v From (
  Select 'a' As v From dual
  Union All
  Select 'a' From dual
  Union All
  Select 'b' From dual
)
Union
Select v From (
  Select 'b' As v From dual
  Union All
  Select 'b' From dual
  Union All
  Select 'c' From dual
)
Minus
Select v From (
  Select 'b' As v From dual
  Union All
  Select 'b' From dual
  Union All
  Select 'c' From dual
)
Intersect
Select v From (
  Select 'b' As v From dual
  Union All
  Select 'b' From dual
  Union All
  Select 'c' From dual
)

결과)
V
--
?

 

Posted by sangheum

댓글을 달아 주세요