UNION / INTERSECT / MINUS

1) 결과를 모아서 출력하기 - UNION / UNION ALL

UNION - 합집합 (중복처리O)
UNION ALL - 합집합 (중복처리X, 모든 값들을 보여줌)

SELECT [컬럼명, 컬럼명 ...]
FROM [테이블명]
WHERE [조건]
UNION
SELECT [컬럼명, 컬럼명 ...
FROM [테이블명]
WHERE [조건]

SELECT [컬럼명, 컬럼명 ...]
FROM [테이블명]
WHERE [조건]
UNION ALL
SELECT [컬럼명, 컬럼명 ...]
FROM [테이블명]
WHERE [조건]

EX)
SELECT DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE SALARY > 1000
AND DEPARTMENT_ID = 90
UNION / UNION ALL
SELECT DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE SALARY > 1000
AND DEPARTMENT_ID = 80;

 

2) 공통 데이터 찾기 - INTERSECT
두 SQL 문장의 실행 결과에서 공통부분(교집합)을 찾는 연산

SELECT [컬럼명A] 
FROM [테이블명] 
WHERE [조건B] 
INTERSECT 
SELECT [컬럼명A] 
FROM [테이블명] 
WHERE [조건B] 

EX)
SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID >= 70
INTERSECT
SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID < 70;

 

 

3) 특정 결과를 재외하기 - MINUS
큰 집합에서 작은 집합 빼는(차집합) 연산

/* 큰 집합 */
SELECT [컬럼명A]   
FROM [테이블명]   

MINUS 

/* 작은 집합 */
SELECT [컬럼명A]   
FROM [테이블명] 
WHERE [조건];