Database/MySQL

[MySQL] UNION (distinct) vs UNION ALL 차이

데브쥬쥬 2023. 6. 25. 22:59
  • 공통점
    • 두 문법 모두 쿼리 결과를 합쳐준다
    • 둘다 그리 좋은 SQL은 아니고, 테이블을 통합하는게 좋음
    • UNION ALL 보다는 UNION 중복 제거하는 쿼리가 당연히 느림
  • UNION (distinct)
    • 중복을 제거하여 합쳐준다
SELECT 1 AS COL1, 100 AS MONEY UNION 
SELECT 1 AS COL1, 100 AS MONEY UNION 
SELECT 2 AS COL1, 200 AS MONEY UNION 
SELECT 2 AS COL1, 200 AS MONEY UNION 
SELECT 3 AS COL1, 300 AS MONEY UNION 
SELECT 3 AS COL1, 300 AS MONEY UNION 
SELECT 4 AS COL1, 400 AS MONEY

  • UNION ALL
    • 중복을 제거하지 않고 모두 합친다
SELECT 1 AS COL1, 100 AS MONEY UNION 
SELECT 1 AS COL1, 100 AS MONEY UNION 
SELECT 2 AS COL1, 200 AS MONEY UNION 
SELECT 2 AS COL1, 200 AS MONEY UNION 
SELECT 3 AS COL1, 300 AS MONEY UNION 
SELECT 3 AS COL1, 300 AS MONEY UNION 
SELECT 4 AS COL1, 400 AS MONEY