UNION ALL, UNION

SELECT * FROM DEPT WHERE DEPTNO IN (10,20,30)
UNION ALL 
SELECT * FROM DEPT WHERE DEPTNO IN (30,40,50);

SELECT * FROM DEPT WHERE DEPTNO IN (10,20,30)
UNION 
SELECT * FROM DEPT WHERE DEPTNO IN (30,40,50)

----------------------------------------------

WITH TMP AS
(
SELECT * FROM DEPT WHERE DEPTNO IN (10,20,30)
UNION ALL 
SELECT * FROM DEPT WHERE DEPTNO IN (30,40,50)
)
SELECT DISTINCT * FROM TMP ORDER BY DEPTNO

차이점은 중복 제거 여부다. 

따라서 나온 결과에 DISTINCT 를 먹인 것과 같다.

 

만약 중복제거가 불필요하다면 UNION ALL을 사용해 불필요한 연산을 피하는 것이 좋다.