개발/오라클 SQL
UNION ALL, UNION
제로칼로리
2022. 12. 28. 17:45
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을 사용해 불필요한 연산을 피하는 것이 좋다.