DISTINCT vs GROUP BY — cùng plan, khác intent

Hai query trả về cùng kết quả: SELECT DISTINCT user_id FROM tasks và SELECT user_id FROM tasks GROUP BY user_id. Chạy EXPLAIN cho cả hai — plan giống hệt: HashAggregate. Vậy khác gì nhau?

Bài này chỉ ra sự khác biệt là semantic intent chứ không phải hiệu năng, giải thích khi nào nên chọn cái nào, và giới thiệu DISTINCT ON — cú pháp PostgreSQL-specific giải bài "top N per group" trong một dòng mà không cần window function (kèm alternative portable cho các engine khác).

1. Analogy — "lấy danh sách" vs "gom nhóm để đếm"

Hình dung bảng chấm công: mỗi nhân viên có nhiều dòng ứng với nhiều ngày làm việc. Bạn cần hai thứ khác nhau:

Yêu cầu A: "Cho tôi danh sách nhân viên đã từng chấm công." Intent: loại bỏ trùng lặp.

Yêu cầu B: "Cho tôi số ngày làm việc của mỗi nhân viên." Intent: gom nhóm để tính tổng hợp.

2. Cơ chế — DISTINCT vs GROUP BY

flowchart TD
  INPUT["Input rows\n(co the co duplicate)"]
  INPUT --> D["DISTINCT"]
  INPUT --> G["GROUP BY"]
  D --> D1["Hash / sort tat ca rows"]
  D1 --> D2["Giu 1 row dai dien\ncho moi gia tri unique"]
  D2 --> D3["Tra danh sach khong trung\n(khong co aggregate)"]
  G --> G1["Hash / sort tat ca rows"]
  G1 --> G2["Gom nhom rows\ncung gia tri GROUP BY"]
  G2 --> G3["Ap dung aggregate\n(COUNT, SUM, AVG...)"]
  G3 --> G4["Tra nhom + ket qua aggregate"]
  style D3 fill:#dbeafe,color:#1e3a8a
  style G4 fill:#ede9fe,color:#4c1d95

3. Cùng plan, khác intent

-- Ca hai query tra ve ket qua giong het nhau
SELECT DISTINCT user_id FROM tasks;
SELECT user_id FROM tasks GROUP BY user_id;

Khi xem execution plan của cả hai query (dùng EXPLAIN hoặc công cụ tương đương trên engine của bạn), plan thường giống hệt nhau — hệ quản trị biến DISTINCT col thành cùng một phép toán gom nhóm với GROUP BY col. Ví dụ trên PostgreSQL:

-- Ca hai deu cho plan: HashAggregate (Group Key: user_id) -> Seq Scan
EXPLAIN SELECT DISTINCT user_id FROM tasks;
EXPLAIN SELECT user_id FROM tasks GROUP BY user_id;
-- Cost giong nhau -- chi khac semantic intent khi doc code

Hệ quản trị nhận ra rằng dedupe (loại trùng) và group-then-return-key là cùng một phép toán — cả hai đều cần gom các row có cùng giá trị lại và giữ một đại diện. Cost giống nhau, execution plan giống nhau.

Sự khác biệt nằm ở readability và extensibility:

• Dùng DISTINCT khi chỉ cần dedupe một hoặc vài column — intent rõ ràng cho người đọc code.
• Dùng GROUP BY khi cần aggregate (COUNT, SUM, AVG) hoặc filter sau aggregate (HAVING).

Demo bắt buộc dùng GROUP BY khi thêm aggregate:

-- DISTINCT khong the lam duoc dieu nay
SELECT user_id, COUNT(*) AS task_count FROM tasks GROUP BY user_id;

-- Loi neu co gang them aggregate vao DISTINCT
-- ERROR: aggregate functions are not allowed in DISTINCT ON expressions

4. DISTINCT ON — cú pháp PostgreSQL cho "top N per group"

Bài toán: "lấy task gần nhất của mỗi user". Giải pháp portable là window function (Module 3 — JOIN, aggregation & window sẽ đi sâu). Với PostgreSQL, có cách ngắn hơn: DISTINCT ON.

-- Lay task moi nhat cho moi user
SELECT DISTINCT ON (assignee_id) *
FROM tasks
WHERE assignee_id IS NOT NULL
ORDER BY assignee_id, created_at DESC;

Cú pháp: DISTINCT ON (col1, col2) * giữ lại row đầu tiên trong mỗi nhóm unique theo col1, col2 — theo thứ tự ORDER BY quyết định row nào là "đầu tiên".

Quan trọng: column trong DISTINCT ON (...) phải là leftmost prefix của ORDER BY. Nếu DISTINCT ON (assignee_id) thì ORDER BY phải bắt đầu bằng assignee_id.

Tương đương với window function (verbose hơn nhiều):

-- Window function equivalent -- dai hon nhung portable sang MySQL/SQLite
SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY assignee_id ORDER BY created_at DESC) AS rn
  FROM tasks
  WHERE assignee_id IS NOT NULL
) sub
WHERE rn = 1;

DISTINCT ON ngắn hơn và planner thường tối ưu tốt hơn cho case đơn giản vì có thể tận dụng composite index trên (assignee_id, created_at DESC).

Hạn chế: DISTINCT ON chỉ có trong PostgreSQL. MySQL, SQLite, SQL Server không hỗ trợ — phải dùng window function khi cần portable.

5. Pitfall — DISTINCT * trên large table

Ví dụ anti-pattern kinh điển:

-- ANTI-PATTERN: dung DISTINCT de hide JOIN bug
-- Implicit cross join: moi user ghep voi moi task -> duplicate
SELECT DISTINCT u.*
FROM users u, tasks t
WHERE u.id = t.assignee_id;

-- FIX: explicit JOIN, khong can DISTINCT
SELECT u.*
FROM users u
INNER JOIN tasks t ON u.id = t.assignee_id;
-- Van co the co duplicate neu user co nhieu task -- can DISTINCT hoac GROUP BY dung nghia
-- Fix hoan chinh:
SELECT DISTINCT u.*
FROM users u
INNER JOIN tasks t ON u.id = t.assignee_id;
-- Hoac:
SELECT u.* FROM users u WHERE EXISTS (
  SELECT 1 FROM tasks t WHERE t.assignee_id = u.id
);

Nguyên tắc: nếu bạn đang thêm DISTINCT để "fix" duplicate trong kết quả, hãy hỏi tại sao có duplicate trước — thường là JOIN condition thiếu hoặc sai.

6. GROUP BY — strict mode theo SQL chuẩn

SQL chuẩn (và hầu hết RDBMS hiện đại) yêu cầu mọi column trong SELECT list phải nằm trong GROUP BY clause hoặc được bọc trong aggregate function. MySQL trước 5.7 không enforce rule này — một nguồn gây bug khi migrate.

-- ERROR theo SQL chuan (PostgreSQL, SQL Server, SQLite strict mode...)
SELECT user_id, title FROM tasks GROUP BY user_id;
-- ERROR: column "tasks.title" must appear in the GROUP BY clause
--        or be used in an aggregate function

-- Fix 1: them title vao GROUP BY
SELECT user_id, title FROM tasks GROUP BY user_id, title;

-- Fix 2: dung aggregate function
-- STRING_AGG (PostgreSQL, SQL Server 2017+) hoac GROUP_CONCAT (MySQL/SQLite)
SELECT user_id, STRING_AGG(title, ', ') AS all_titles FROM tasks GROUP BY user_id;

MySQL trước 5.7 chạy thành công câu query lỗi trên nhưng trả về giá trị title ngẫu nhiên (không xác định row nào được chọn) — silent bug khó phát hiện. Hệ quản trị tuân thủ strict mode sẽ fail ngay tại parse time, đây là hành vi đúng đắn hơn.

Module 3 — JOIN, aggregation & window của khoá này sẽ đi sâu vào GROUP BY kết hợp HAVING và các visibility rule của aggregate.

7. Applied — TaskFlow scenarios

Ba tình huống thực tế cho thấy khi nào chọn gì:

-- Scenario 1: list user da tung comment trong project X
-- DISTINCT phu hop: chi can dedupe, khong can count
SELECT DISTINCT u.id, u.name
FROM users u
JOIN comments c ON c.user_id = u.id
JOIN tasks t ON t.id = c.task_id
WHERE t.project_id = 5;

-- Scenario 2: count so comment moi user trong project X
-- GROUP BY bat buoc: can aggregate COUNT
SELECT u.name, COUNT(c.id) AS comment_count
FROM users u
JOIN comments c ON c.user_id = u.id
JOIN tasks t ON t.id = c.task_id
WHERE t.project_id = 5
GROUP BY u.id, u.name
ORDER BY comment_count DESC;

-- Scenario 3: lay comment moi nhat cua moi user trong project X
-- DISTINCT ON elegant: top 1 per group
SELECT DISTINCT ON (user_id) user_id, body, created_at
FROM comments
WHERE task_id IN (SELECT id FROM tasks WHERE project_id = 5)
ORDER BY user_id, created_at DESC;

8. Deep Dive — DISTINCT semantics

9. Liên hệ các bài khác

• Bài 02 — WHERE + NULL three-valued logic: NULL trong GROUP BY column — rows có giá trị NULL được gom thành một nhóm riêng, cùng nguyên lý NULL không bằng nhau.
• Bài 03 — ORDER BY + pagination: stable sort cần tiebreaker — cùng nguyên lý với GROUP BY cần xác định rõ column nào được chọn trong nhóm.
• Bài 05 — Pattern matching: DISTINCT thường xuất hiện cùng WHERE có LIKE — biết khi nào dùng DISTINCT vs EXISTS để tránh Cartesian product.
• Module 3 — JOIN, aggregation & window: GROUP BY + HAVING, window function thay thế DISTINCT ON khi cần portable — mở rộng trực tiếp từ bài này.

10. Tóm tắt

• DISTINCT và GROUP BY có cùng plan (HashAggregate) khi chỉ dedupe một column — khác biệt là semantic intent, không phải performance.
• Dùng DISTINCT khi chỉ cần danh sách không trùng, không cần tổng hợp số liệu — intent rõ ràng hơn cho người đọc code.
• Dùng GROUP BY khi cần aggregate (COUNT, SUM, AVG) hoặc filter qua HAVING.
• DISTINCT ON (col) là PG-specific — giữ row đầu tiên mỗi nhóm theo ORDER BY, giải bài "top N per group" ngắn gọn mà không cần window function. Column trong DISTINCT ON phải là leftmost prefix của ORDER BY.
• DISTINCT * thường là dấu hiệu JOIN sai — debug nguyên nhân duplicate thay vì patch bằng DISTINCT.
• SQL strict mode: mọi column trong SELECT phải nằm trong GROUP BY hoặc aggregate (SQL chuẩn, PostgreSQL, SQL Server, SQLite strict) — MySQL trước 5.7 lax, port sang engine strict sẽ fail.
• Forward link: Module 3 — JOIN, aggregation & window sẽ đi sâu vào GROUP BY + HAVING + aggregate visibility rules, và window function thay thế DISTINCT ON khi cần portable.

11. Tự kiểm tra

SELECT u.*
FROM users u
WHERE EXISTS (
SELECT 1 FROM tasks t WHERE t.assignee_id = u.id
);

-- Fix 1: them title vao GROUP BY
SELECT user_id, title FROM tasks GROUP BY user_id, title;

-- Fix 2: dung aggregate
SELECT user_id, MIN(title) AS first_title FROM tasks GROUP BY user_id;

Bài tiếp theo: Pattern matching — vì sao LIKE '%x' kill index