Set Operations — UNION, INTERSECT, EXCEPT

TL;DR: Ba phép toán tập hợp chuẩn SQL gộp/giao/trừ hai tập row có cùng cấu trúc. UNION gộp + dedupe; UNION ALL gộp giữ duplicate (nhanh hơn vì bỏ bước dedupe). INTERSECT lấy phần chung, EXCEPT lấy phần riêng của tập thứ nhất (Oracle gọi là MINUS). Hai điểm dễ sai: ORDER BY chỉ đặt ở ngoài cùng, và NULL trong set operation coi NULL = NULL là TRUE (khác WHERE clause). INTERSECT/EXCEPT thường rewrite được thành EXISTS/NOT EXISTS để tận dụng index. Cả ba là cú pháp ANSI, nhưng mức hỗ trợ khác nhau — ghi chú dialect khi cần (vd MySQL chỉ thêm INTERSECT/EXCEPT từ phiên bản gần đây).

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. Venn diagram — 4 phép toán tập hợp

flowchart LR
    subgraph A["Tap A (comments.user_id)"]
        A1([u1]) & A2([u2]) & A3([u3])
    end
    subgraph B["Tap B (tasks.assignee_id)"]
        B2([u2]) & B3([u3]) & B4([u4])
    end

    UNION["UNION\nA + B dedupe\n-> u1 u2 u3 u4"]
    UNION_ALL["UNION ALL\nA + B giu dup\n-> u1 u2 u3 u3 u2 u3 u4"]
    INTERSECT["INTERSECT\nA giao B\n-> u2 u3"]
    EXCEPT["EXCEPT\nA tru B\n-> u1"]

    A --> UNION & UNION_ALL & INTERSECT & EXCEPT
    B --> UNION & UNION_ALL & INTERSECT & EXCEPT

3. UNION vs UNION ALL

-- UNION: dedupe (engine phai sort hoac hash de loai trung)
-- 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;

-- Query plan: co buoc dedupe (sort+unique hoac hash) -- 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;

-- Query plan: chi noi ket qua (append), khong sort/dedupe -> 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.

4. 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. Engine thực hiện bằng hash hoặc sort — tương tự DISTINCT. (Ghi chú dialect: INTERSECT là chuẩn SQL nhưng MySQL chỉ hỗ trợ từ phiên bản gần đây — engine cũ phải thay bằng EXISTS như dưới đây.)

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.

5. 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. (Như INTERSECT, EXCEPT là chuẩn SQL nhưng MySQL chỉ thêm gần đây; engine cũ thay bằng NOT EXISTS.)

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;

6. 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.

7. 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 CAST(user_id AS TEXT) FROM comments  -- text
UNION ALL
SELECT CAST(id AS 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

Engine 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. Quy tắc type compatibility là chuẩn SQL, nhưng mức "lỏng" của implicit cast khác nhau giữa engine — viết explicit cast là cách an toàn portable nhất.

8. 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

9. 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.
• Query plan cho thấy INTERSECT/EXCEPT dùng scan toàn bộ + sort — EXISTS thường chọn được 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.

10. 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 chỉ là phép nối (append) — planner ghép 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.

11. Deep Dive

12. Tóm tắt

• UNION ALL nối kết quả 2 query và giữ nguyên duplicate — chỉ là phép append, không sort. Nhanh hơn UNION rõ rệt.
• UNION = UNION ALL + dedupe (engine sort hoặc hash để loại trùng) — 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.

13. 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 hoặc UPDATE khi key trùng