UNION ALL과 UNION 비교(중복 제거 비용)

차이점

UNION ALL이 더 빠릅니다

예시: 양쪽 데이터가 서로 겹치지 않을 때

SELECT * FROM active_orders
UNION ALL
SELECT * FROM archived_orders;

예시: 중복이 중요할 때

SELECT order_id FROM email_events
UNION ALL
SELECT order_id FROM push_events;

예시: 중복을 제거하려는 경우

SELECT email FROM list_a
UNION
SELECT email FROM list_b;

서로 다른 원본과 UNION ALL 사용하기

SELECT 'login' AS event, ts, user_id FROM logins
UNION ALL
SELECT 'signup',          ts, user_id FROM signups
UNION ALL
SELECT 'purchase',        ts, user_id FROM purchases
ORDER BY ts DESC LIMIT 100;

각 하위 SELECT 내부의 순서

SELECT ... FROM a
UNION ALL
SELECT ... FROM b
ORDER BY ts DESC;

각 부분에 LIMIT 적용하기

(SELECT id FROM new_orders ORDER BY created_at DESC LIMIT 10)
UNION ALL
(SELECT id FROM old_orders ORDER BY created_at DESC LIMIT 10);

쿼리 계획기가 UNION을 최적화하는 경우

UNION과 OR 비교

-- Slow OR (may not use either index):
SELECT * FROM users WHERE city = 'NYC' OR country = 'DE';

-- Sometimes faster:
SELECT * FROM users WHERE city = 'NYC'
UNION
SELECT * FROM users WHERE country = 'DE';

열 순서 확인

복습

빠른 확인