Set Operations — UNION, INTERSECT, EXCEPT

Bạn cần tìm "user vừa comment vừa tạo task trong tuần này". Một cách là viết JOIN phức tạp với 2 bảng và GROUP BY. Cách kia: INTERSECT — 1 dòng, đọc như đề bài.

Set operations (UNION, INTERSECT, EXCEPT) xử lý 3 use case kinh điển mà JOIN làm được nhưng verbose hơn: gộp hai tập, lấy phần giao, lấy phần hiệu. Bài này đi qua cả 3, kèm performance rule, NULL semantics bất ngờ, và một pitfall về ORDER BY scope mà ít tài liệu nhắc đến.

1. Analogy — Venn diagram 2 vòng tròn

Hình dung 2 vòng tròn Venn: vòng A là "user đã comment", vòng B là "user đã tạo task".

2. UNION vs UNION ALL

-- UNION: dedupe (Sort + Unique hoac HashAggregate)
-- Lay danh sach user_id xuat hien trong comments HOAC tasks
SELECT user_id FROM comments
UNION
SELECT assignee_id FROM tasks WHERE assignee_id IS NOT NULL;

-- EXPLAIN se thay HashAggregate hoac Sort + Unique node -- cost cao hon

-- UNION ALL: gop, giu duplicate -- khong dedupe
SELECT user_id FROM comments
UNION ALL
SELECT assignee_id FROM tasks WHERE assignee_id IS NOT NULL;

-- EXPLAIN: Append node, khong co Sort/Unique -> nhanh hon

UNION thực ra là UNION ALL + thêm bước sort/hash để loại duplicate — cost cao hơn rõ rệt trên tập lớn.

Khi nào nên dùng UNION ALL:

• Source set không overlap về thực tế (vd "comment events" vs "login events" — id row không thể trùng nhau về nghĩa).
• Bạn muốn đếm "tổng activity" = số comment + số login (cần giữ duplicate để count đúng).
• Performance critical — bảng lớn, nhiều source.

Khi nào cần UNION (dedupe):

• Hai query có thể trả trùng cùng một user_id và bạn thực sự muốn mỗi user xuất hiện 1 lần.
• Chỉ khi semantic yêu cầu dedupe — không dùng như mặc định.

Recommendation: luôn dùng UNION ALL trừ khi thật sự cần dedupe. Thêm UNION để dedupe sau dễ hơn là optimize UNION chậm về sau.

3. INTERSECT — set giao

-- User vua comment vua la assignee cua task nao do
SELECT user_id FROM comments
INTERSECT
SELECT assignee_id FROM tasks WHERE assignee_id IS NOT NULL;

-- Equivalent EXISTS -- thuong nhanh hon tren index tot:
SELECT DISTINCT c.user_id
FROM comments c
WHERE EXISTS (
  SELECT 1 FROM tasks t WHERE t.assignee_id = c.user_id
);

INTERSECT trả về các row xuất hiện trong cả hai query. PostgreSQL thực hiện bằng hash hoặc sort — tương tự DISTINCT.

INTERSECT ALL — giữ multiplicity (row xuất hiện min(count_left, count_right) lần):

-- Neu user_id=7 xuat hien 3 lan trong comments va 2 lan trong tasks:
-- INTERSECT    -> user_id=7 xuat hien 1 lan
-- INTERSECT ALL -> user_id=7 xuat hien min(3,2) = 2 lan
SELECT user_id FROM comments
INTERSECT ALL
SELECT assignee_id FROM tasks WHERE assignee_id IS NOT NULL;

INTERSECT ALL ít dùng trong thực tế — chủ yếu hữu ích khi bạn cần preserve multiplicity cho phép toán tập hợp chính xác.

4. EXCEPT — set hiệu

-- User da comment nhung chua bao gio la assignee cua bat ky task nao
SELECT user_id FROM comments
EXCEPT
SELECT assignee_id FROM tasks WHERE assignee_id IS NOT NULL;

-- Equivalent NOT EXISTS -- thuong nhanh hon:
SELECT DISTINCT c.user_id
FROM comments c
WHERE NOT EXISTS (
  SELECT 1 FROM tasks t WHERE t.assignee_id = c.user_id
);

EXCEPT trả về các row trong query 1 mà không xuất hiện trong query 2. Oracle gọi phép toán này là MINUS — cùng semantic, khác tên.

EXCEPT ALL — giữ multiplicity (row còn lại = count_left - count_right, nếu > 0):

-- Neu user_id=7 xuat hien 3 lan trong comments, 1 lan trong tasks:
-- EXCEPT    -> user_id=7 xuat hien 1 lan
-- EXCEPT ALL -> user_id=7 xuat hien 3-1 = 2 lan
SELECT user_id FROM comments
EXCEPT ALL
SELECT assignee_id FROM tasks WHERE assignee_id IS NOT NULL;

5. ALL variant — ví dụ multiplicity rõ

-- Gia su:
-- table A: [1, 2, 2, 3]
-- table B: [2, 3, 3, 4]

-- A INTERSECT B     -> {2, 3}         (dedupe ca hai phia truoc so sanh)
-- A INTERSECT ALL B -> {2, 3}         (min count: 2 xuat hien 2x/1x -> 1; 3 xuat hien 1x/2x -> 1)
-- A EXCEPT B        -> {1}            (dedupe: 2 co trong B; 3 co trong B)
-- A EXCEPT ALL B    -> {1, 2}         (2: 2 lan - 1 lan = 1 con; 3: 1 lan - 2 lan = 0; 1: 1-0=1)

Rule nhớ nhanh: variant không có ALL thực hiện dedupe trên cả hai tập trước khi so sánh. Variant ALL giữ số lần xuất hiện theo phép trừ/min.

6. Constraint — column count và type

-- Column count phai bang nhau cross queries
SELECT user_id, created_at FROM comments   -- 2 columns
UNION ALL
SELECT assignee_id, created_at FROM tasks; -- 2 columns -- OK

-- Type phai compatible -- PG cast tu dong neu co the
SELECT user_id::text FROM comments  -- text
UNION ALL
SELECT id::text FROM tasks;         -- text -- OK

-- Column name lay tu query dau tien
SELECT user_id AS actor_id FROM comments   -- ten column trong result: "actor_id"
UNION ALL
SELECT assignee_id FROM tasks;             -- ten column trong tasks bi bo qua

PostgreSQL tự cast type nếu có implicit cast (ví dụ int và bigint). Nếu không có implicit cast, bạn cần explicit cast trong từng query.

7. Pitfall — ORDER BY scope và NULL semantics

-- OK: ORDER BY nam o ngoai cung, sau tat ca UNION ALL
SELECT user_id FROM comments
UNION ALL
SELECT assignee_id FROM tasks WHERE assignee_id IS NOT NULL
ORDER BY user_id;

-- ERROR: ORDER BY ben trong subquery cua UNION
(SELECT user_id FROM comments ORDER BY user_id)
UNION ALL
(SELECT assignee_id FROM tasks WHERE assignee_id IS NOT NULL);
-- ERROR: ORDER BY ben trong subquery chi duoc phep neu co LIMIT/OFFSET di kem

-- Trong WHERE clause: NULL = NULL -> UNKNOWN (khong match)
-- Trong set operations: NULL = NULL -> TRUE (match, giong DISTINCT)

-- Vi du:
-- comments.user_id co row voi NULL
-- tasks.assignee_id co row voi NULL
SELECT user_id FROM comments
INTERSECT
SELECT assignee_id FROM tasks WHERE assignee_id IS NOT NULL;
-- Neu WHERE assignee_id IS NOT NULL loai NULL khoi tasks,
-- NULL tu comments khong the match -> khong xuat hien trong ket qua

-- Nhung neu ca hai query deu co NULL:
SELECT user_id FROM comments   -- co NULL
INTERSECT
SELECT assignee_id FROM tasks; -- co NULL (khong filter)
-- NULL tu comments MATCH NULL tu tasks -> NULL xuat hien trong ket qua
-- Khac voi WHERE: NULL != NULL; trong set ops: NULL "=" NULL

8. Performance — rewrite thành EXISTS khi cần index

UNION ALL luôn nhanh hơn UNION vì không có bước sort/dedupe. Với INTERSECT và EXCEPT, thường có thể rewrite thành EXISTS/NOT EXISTS với plan tốt hơn nhờ index:

-- INTERSECT: A INTERSECT B
-- Rewrite thanh EXISTS -- planner co the dung index tren assignee_id
SELECT DISTINCT c.user_id
FROM comments c
WHERE EXISTS (
  SELECT 1 FROM tasks t WHERE t.assignee_id = c.user_id
);

-- EXCEPT: A EXCEPT B
-- Rewrite thanh NOT EXISTS
SELECT DISTINCT c.user_id
FROM comments c
WHERE NOT EXISTS (
  SELECT 1 FROM tasks t WHERE t.assignee_id = c.user_id
);

Khi nào rewrite:

• Bảng lớn và cần tận dụng index trên join column.
• EXPLAIN cho thấy INTERSECT/EXCEPT dùng Seq Scan + Sort — EXISTS thường chọn Index Scan.
• Query phức tạp hơn 2 column — INTERSECT match toàn bộ row, EXISTS cho phép join chọn lọc column.

9. Applied — TaskFlow user activity feed

Combine 4 nguồn event thành 1 timeline — đây là pattern kinh điển cho audit log / activity feed:

-- Activity feed cua user $1: 4 loai event gop lai bang UNION ALL
SELECT
  'commented'     AS event_type,
  c.user_id,
  c.created_at    AS event_at,
  'task: ' || t.title AS detail
FROM comments c
JOIN tasks t ON t.id = c.task_id
WHERE c.user_id = $1

UNION ALL

SELECT
  'created_task',
  assignee_id,
  created_at,
  'created: ' || title
FROM tasks
WHERE assignee_id = $1

UNION ALL

SELECT
  'completed_task',
  assignee_id,
  updated_at,
  'completed: ' || title
FROM tasks
WHERE assignee_id = $1 AND status = 'done'

UNION ALL

SELECT
  'login',
  user_id,
  created_at,
  'logged in'
FROM user_sessions
WHERE user_id = $1

ORDER BY event_at DESC
LIMIT 50;

Tại sao UNION ALL thay vì UNION ở đây:

• 4 event type khác nhau về nghĩa — "commented" và "created_task" là 2 event riêng dù cùng user_id và timestamp.
• Không có duplicate cần loại bỏ — mỗi row từ mỗi source là event riêng biệt.
• UNION ALL = Append node trong EXPLAIN — planner chỉ nối kết quả 4 subquery, không sort/hash thêm.
• ORDER BY và LIMIT ở ngoài cùng — áp dụng lên toàn bộ kết quả merged.

Index đề xuất: (user_id, created_at DESC) trên mỗi bảng — cho phép từng subquery dùng Index Scan trước khi merge, thay vì Seq Scan toàn bảng rồi mới filter.

10. Deep Dive

11. Tóm tắt

• UNION ALL nối kết quả 2 query và giữ nguyên duplicate — planner dùng Append node, không sort. Nhanh hơn UNION rõ rệt.
• UNION = UNION ALL + dedupe (Sort + Unique hoặc HashAggregate) — chỉ dùng khi thực sự cần loại trùng lặp.
• INTERSECT trả row xuất hiện ở cả 2 query (dedupe). INTERSECT ALL giữ min(count_left, count_right) lần.
• EXCEPT trả row trong query 1 mà không có trong query 2. EXCEPT ALL giữ count_left - count_right lần (nếu > 0). Oracle gọi là MINUS.
• Constraint: số column phải bằng nhau, type phải compatible, tên column lấy từ query đầu tiên.
• ORDER BY chỉ cho phép ở ngoài cùng — không thể ORDER BY bên trong từng subquery của UNION (trừ khi có LIMIT đi kèm).
• NULL semantics: trong set operations, NULL = NULL là TRUE (giống DISTINCT) — khác với WHERE clause nơi NULL = NULL là UNKNOWN.
• Performance: INTERSECT/EXCEPT có thể rewrite thành EXISTS/NOT EXISTS để tận dụng index tốt hơn.

12. Tự kiểm tra

SELECT user_id FROM comments
EXCEPT
SELECT assignee_id FROM tasks WHERE assignee_id IS NOT NULL;

SELECT DISTINCT c.user_id
FROM comments c
WHERE NOT EXISTS (
SELECT 1 FROM tasks t WHERE t.assignee_id = c.user_id
);

-- comments.user_id co row NULL (user chua dang nhap)
-- tasks.assignee_id co row NULL (task chua assign)
SELECT user_id FROM comments   -- co NULL
INTERSECT
SELECT assignee_id FROM tasks; -- co NULL

-- Ket qua co chua NULL!
-- NULL tu comments "match" NULL tu tasks trong set op
-- Nhung trong WHERE: WHERE c.user_id = t.assignee_id -> NULL rows bi loai

Bài tiếp theo: UPSERT — INSERT ... ON CONFLICT idempotent