12.4 — 3. Queries Nâng Cao

Tóm tắt

Bốn công cụ và bốn hiểu lầm đi kèm. LEFT JOIN biến thành INNER JOIN khi bạn đặt điều kiện lên bảng bên phải trong WHERE thay vì trong ON — truy vấn chạy, trả ít dòng hơn, và không ai báo lỗi. CTE không phải bảng tạm: nó thường được nội tuyến vào truy vấn chính, và một CTE bị tham chiếu hai lần có thể thực thi hai lần. Window function chạy sau WHERE, nên không lọc được theo kết quả của nó — phải bọc trong CTE. Và APPLY/LATERAL là công cụ duy nhất cho bài toán "N bản ghi mới nhất của mỗi nhóm".

Mục tiêu bài học

Sau bài này bạn có thể:

• Đặt điều kiện đúng chỗ giữa ON và WHERE.
• Biết khi nào CTE giúp và khi nào nó hại hiệu năng.
• Dùng window function và lọc theo kết quả của chúng.
• Giải bài toán "top N mỗi nhóm" bằng APPLY/LATERAL.
• Đọc truy vấn phức tạp mà không bị nhầm thứ tự thực thi.

Nội dung bài học

12.4.1 — Bẫy LEFT JOIN với WHERE

-- MONG MUỐN: mọi lead, kèm tên nhân viên nếu có, nhưng chỉ nhân viên đang làm
SELECT l.LeadId, l.CompanyName, u.FullName
FROM   Leads l
LEFT JOIN Users u ON l.AssignedToUserId = u.UserId
WHERE  u.IsActive = 1;                          -- SAI

Truy vấn này không trả lead chưa gán. Với lead đó, u.IsActive là NULL, và NULL = 1 cho UNKNOWN — dòng bị loại. LEFT JOIN vừa biến thành INNER JOIN.

-- ĐÚNG: điều kiện về bảng bên phải nằm trong ON
SELECT l.LeadId, l.CompanyName, u.FullName
FROM   Leads l
LEFT JOIN Users u ON l.AssignedToUserId = u.UserId AND u.IsActive = 1;

Quy tắc: điều kiện về bảng bên phải của LEFT JOIN thuộc về ON; chỉ điều kiện về bảng bên trái mới thuộc về WHERE.

Ngoại lệ có chủ đích — tìm "lead chưa gán ai":

SELECT l.LeadId, l.CompanyName
FROM   Leads l
LEFT JOIN Users u ON l.AssignedToUserId = u.UserId
WHERE  u.UserId IS NULL;                        -- anti-join, CO CHU DICH

Đây là mẫu anti-join và là cách dùng WHERE sau LEFT JOIN duy nhất đúng.

Một bẫy NULL liên quan:

-- SAI — NOT IN với NULL trong tập con trả về RỖNG
SELECT * FROM Users
WHERE UserId NOT IN (SELECT AssignedToUserId FROM Leads);   -- có NULL -> không ra gì

-- DUNG
SELECT * FROM Users u
WHERE NOT EXISTS (SELECT 1 FROM Leads l WHERE l.AssignedToUserId = u.UserId);

NOT IN với một NULL trong danh sách trả về tập rỗng, vì x <> NULL là UNKNOWN. NOT EXISTS không có vấn đề này. Mặc định dùng NOT EXISTS.

12.4.2 — CTE không phải bảng tạm

WITH MonthlyRevenue AS (
    SELECT DATEFROMPARTS(YEAR(ConvertedAt), MONTH(ConvertedAt), 1) AS MonthStart,
           SUM(EstimatedValue) AS Revenue
    FROM   Leads
    WHERE  Status = 'Won' AND ConvertedAt IS NOT NULL
    GROUP  BY DATEFROMPARTS(YEAR(ConvertedAt), MONTH(ConvertedAt), 1)
)
SELECT MonthStart, Revenue,
       LAG(Revenue) OVER (ORDER BY MonthStart) AS PrevMonth
FROM   MonthlyRevenue;

CTE làm truy vấn dễ đọc — đó là giá trị chính. Nhưng hai hiểu lầm phổ biến:

1. CTE không vật chất hoá kết quả. Trong SQL Server, nó thường được nội tuyến vào truy vấn chính như một subquery. Tham chiếu CTE hai lần có thể khiến nó thực thi hai lần:

WITH Expensive AS (SELECT ... FROM BigTable WHERE <tính toán nặng>)
SELECT * FROM Expensive a JOIN Expensive b ON ...;   -- co the chay HAI LAN

Cần vật chất hoá thật thì dùng bảng tạm:

SELECT ... INTO #Expensive FROM BigTable WHERE <tính toán nặng>;
CREATE INDEX IX_Tmp ON #Expensive(Key);       -- bảng tạm còn INDEX được
SELECT * FROM #Expensive a JOIN #Expensive b ON ...;

Trong PostgreSQL trước phiên bản 12, CTE luôn được vật chất hoá — một rào cản tối ưu hoá. Từ v12 nó nội tuyến theo mặc định, và bạn điều khiển được bằng MATERIALIZED / NOT MATERIALIZED.

2. CTE không nhanh hơn subquery. Chúng thường cho cùng một execution plan. Chọn CTE vì dễ đọc, không vì hiệu năng.

Recursive CTE là thứ subquery không làm được — ví dụ cây tổ chức:

WITH OrgChart AS (
    SELECT UserId, FullName, ManagerId, 0 AS Level
    FROM   Users WHERE ManagerId IS NULL              -- goc

    UNION ALL

    SELECT u.UserId, u.FullName, u.ManagerId, oc.Level + 1
    FROM   Users u
    JOIN   OrgChart oc ON u.ManagerId = oc.UserId     -- de quy
)
SELECT * FROM OrgChart OPTION (MAXRECURSION 100);

MAXRECURSION là lưới an toàn: dữ liệu có vòng lặp (A quản lý B, B quản lý A) sẽ chạy vô tận nếu không có nó.

12.4.3 — Window function

Khác biệt cốt lõi với GROUP BY: window function giữ nguyên số dòng, còn GROUP BY gộp chúng lại.

-- GROUP BY: 6 dòng (một dòng mỗi trạng thái)
SELECT Status, COUNT(*) FROM Leads GROUP BY Status;

-- Window: MỌI dòng, kèm tổng số của trạng thái đó
SELECT LeadId, CompanyName, Status,
       COUNT(*) OVER (PARTITION BY Status) AS TotalInStatus
FROM   Leads;

Bốn nhóm hay dùng:

-- 1. Danh so thu tu
ROW_NUMBER() OVER (PARTITION BY Status ORDER BY EstimatedValue DESC)   -- 1,2,3,4
RANK()       OVER (ORDER BY TotalWon DESC)                             -- 1,2,2,4
DENSE_RANK() OVER (ORDER BY TotalWon DESC)                             -- 1,2,2,3

-- 2. So sánh với dòng trước/sau
LAG(Revenue)  OVER (ORDER BY MonthStart)
LEAD(Revenue) OVER (ORDER BY MonthStart)

-- 3. Tong luy ke
SUM(Revenue) OVER (ORDER BY MonthStart ROWS UNBOUNDED PRECEDING)

-- 4. Trung binh truot 3 thang
AVG(Revenue) OVER (ORDER BY MonthStart ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

ROWS so với RANGE là chi tiết hay bị bỏ qua và gây sai kết quả:

-- ROWS: đếm theo DÒNG vật lý
SUM(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- RANGE: gộp mọi dòng có CÙNG GIÁ TRỊ ORDER BY
SUM(x) OVER (ORDER BY d RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

Với ORDER BY có giá trị trùng, RANGE gộp tất cả dòng trùng vào cùng một khung — kết quả khác hẳn ROWS. Mặc định của SQL khi có ORDER BY mà không khai khung là RANGE UNBOUNDED PRECEDING, nên SUM(...) OVER (ORDER BY d) có thể không cho tổng luỹ kế như bạn nghĩ. Khai rõ ROWS khi muốn đếm theo dòng.

12.4.4 — Lọc theo kết quả window function

-- SAI — không biên dịch được
SELECT LeadId, ROW_NUMBER() OVER (PARTITION BY Status ORDER BY CreatedAt DESC) AS rn
FROM   Leads
WHERE  rn <= 3;

Window function được tính sau WHERE và GROUP BY, gần cuối chuỗi xử lý. Tại thời điểm WHERE chạy, rn chưa tồn tại.

Thứ tự thực thi logic của SQL:

FROM -> WHERE -> GROUP BY -> HAVING -> WINDOW -> SELECT -> ORDER BY -> LIMIT

Hiểu thứ tự này giải thích nhiều lỗi khác: vì sao không dùng được bí danh cột của SELECT trong WHERE, và vì sao HAVING lọc được kết quả GROUP BY còn WHERE thì không.

-- DUNG — boc trong CTE
WITH Ranked AS (
    SELECT LeadId, CompanyName, Status,
           ROW_NUMBER() OVER (PARTITION BY Status ORDER BY CreatedAt DESC) AS rn
    FROM   Leads
)
SELECT LeadId, CompanyName, Status
FROM   Ranked
WHERE  rn <= 3;

Đây là mẫu chuẩn cho "top N mỗi nhóm" và là lý do phổ biến nhất để dùng CTE.

12.4.5 — APPLY và LATERAL

APPLY (SQL Server) và LATERAL (PostgreSQL) cho phép subquery tham chiếu tới cột của bảng bên trái — điều mà JOIN thường không làm được.

-- SQL Server: 3 contact mới nhất của MỖI customer
SELECT c.CustomerId, c.CompanyName, ct.FirstName, ct.LastName
FROM   Customers c
CROSS APPLY (
    SELECT TOP 3 FirstName, LastName
    FROM   Contacts
    WHERE  CustomerId = c.CustomerId          -- tham chieu c
    ORDER  BY CreatedAt DESC
) ct;

-- PostgreSQL
SELECT c.customer_id, c.company_name, ct.first_name, ct.last_name
FROM   customers c
CROSS JOIN LATERAL (
    SELECT first_name, last_name
    FROM   contacts
    WHERE  customer_id = c.customer_id
    ORDER  BY created_at DESC
    LIMIT  3
) ct;

CROSS APPLY loại bỏ dòng bên trái không có kết quả (như INNER JOIN); OUTER APPLY giữ lại với NULL (như LEFT JOIN). LATERAL tương ứng là CROSS JOIN LATERAL và LEFT JOIN LATERAL ... ON true.

APPLY hay ROW_NUMBER cho top N mỗi nhóm?

	ROW_NUMBER + CTE	APPLY / LATERAL
Cách chạy	Đánh số mọi dòng rồi lọc	Chỉ lấy N dòng cho mỗi nhóm
Nhanh hơn khi	Ít nhóm, nhiều dòng mỗi nhóm	Nhiều nhóm, index tốt
Cần index	Có thì tốt	Bắt buộc (CustomerId, CreatedAt DESC)

Với 100.000 khách hàng, APPLY cộng index đúng thường nhanh hơn nhiều — nó đi thẳng tới 3 dòng của mỗi khách hàng thay vì đánh số toàn bộ bảng Contacts.

Nhưng APPLY không có index phù hợp thì ngược lại: nó thành một vòng lặp chạy một truy vấn cho mỗi dòng bên trái — đúng bản chất N+1 (bài viết về N+1).

12.4.6 — Rà lại code của bạn

Danh sách rà soát truy vấn

• •Điều kiện về bảng bên phải của LEFT JOIN nằm trong ON, không nằm trong WHERE.
• •Mọi WHERE sau LEFT JOIN đều là anti-join có chủ đích.
• •Dùng NOT EXISTS thay vì NOT IN với subquery có thể chứa NULL.
• •CTE được dùng vì dễ đọc, không vì tưởng nó nhanh hơn.
• •CTE nặng bị tham chiếu nhiều lần đã được chuyển sang bảng tạm.
• •Recursive CTE có giới hạn độ sâu.
• •Window function có khung ROWS khai rõ khi cần tổng luỹ kế.
• •Lọc theo kết quả window function được bọc trong CTE.
• •APPLY và LATERAL luôn có index hỗ trợ ở bảng bên trong.
• •Truy vấn báo cáo nặng được kiểm tra bằng execution plan, không chỉ bằng kết quả.

Bài tập áp dụng

Bài 1 — LEFT JOIN âm thầm thành INNER JOIN

Viết truy vấn có điều kiện bảng bên phải trong WHERE, đếm số dòng, rồi chuyển điều kiện vào ON và đếm lại.

Tiêu chí hoàn thành: bạn giải thích được bằng thứ tự xử lý mệnh đề, chứ không chỉ nói "phải để trong ON".

Gợi ý và lời giải — Bài 1

Gợi ý. WHERE chạy sau JOIN. Hãy nghĩ xem lúc WHERE chạy thì những dòng không khớp đang mang giá trị gì.

Lời giải — dữ liệu thử:

CREATE TABLE Customers (Id INT PRIMARY KEY, Name NVARCHAR(100));
CREATE TABLE Orders (Id INT PRIMARY KEY, CustomerId INT, Status NVARCHAR(20), Total DECIMAL(18,2));

INSERT INTO Customers VALUES (1,N'Công ty ABC'),(2,N'Startup XYZ'),(3,N'Hộ KD Lan'),(4,N'Tập đoàn BN');
INSERT INTO Orders    VALUES (1,1,N'Paid',5000000),(2,1,N'Cancelled',2000000),(3,2,N'Paid',8000000);

Ba truy vấn, ba kết quả:

-- 1. LEFT JOIN thuần
SELECT c.Name, o.Total
FROM   Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id;

Công ty ABC   5000000
Công ty ABC   2000000
Startup XYZ   8000000
Hộ KD Lan     NULL
Tập đoàn BN   NULL
-- 5 dòng

-- 2. Điều kiện trong WHERE
SELECT c.Name, o.Total
FROM   Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id
WHERE  o.Status = N'Paid';

Công ty ABC   5000000
Startup XYZ   8000000
-- 2 dòng — Hộ KD Lan và Tập đoàn BN BIẾN MẤT

-- 3. Cùng điều kiện, đặt trong ON
SELECT c.Name, o.Total
FROM   Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id AND o.Status = N'Paid';

Công ty ABC   5000000
Startup XYZ   8000000
Hộ KD Lan     NULL
Tập đoàn BN   NULL
-- 4 dòng — ĐÚNG

Giải thích bằng thứ tự xử lý mệnh đề. SQL xử lý logic theo thứ tự này, bất kể bạn viết theo thứ tự nào:

1. FROM / JOIN      <- ghép bảng, LEFT JOIN điền NULL cho phần không khớp
2. WHERE            <- lọc trên KẾT QUẢ đã ghép
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Ở truy vấn 2, sau bước 1 ta có 5 dòng, trong đó hai dòng mang o.Status = NULL (Hộ KD Lan và Tập đoàn BN). Bước 2 kiểm o.Status = N'Paid':

NULL = N'Paid'  ->  UNKNOWN  ->  không phải TRUE  ->  LOẠI

Hai dòng vừa được LEFT JOIN cố tình giữ lại bị chính WHERE vứt đi — và kết quả giống hệt INNER JOIN.

Ở truy vấn 3, điều kiện nằm trong ON nên nó tham gia vào bước 1: nó quyết định dòng bên phải nào được ghép, chứ không quyết định dòng nào được giữ. Dòng bên trái không tìm được cặp vẫn được giữ với NULL — đúng ý nghĩa của LEFT JOIN.

Quy tắc rút ra:

Điều kiện lên	Đặt ở
Bảng bên trái	WHERE
Bảng bên phải	ON

Ngoại lệ duy nhất, và nó là chủ ý:

-- Anti-join: tìm khách hàng CHƯA có đơn nào
SELECT c.Name
FROM   Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id
WHERE  o.Id IS NULL;

Ở đây WHERE lên cột bên phải là đúng, vì bạn muốn giữ đúng những dòng không khớp.

Cách phát hiện trong code có sẵn:

grep -rniE 'LEFT JOIN' --include=*.sql --include=*.cs -A 4 src/ \
  | grep -iE 'WHERE.*\b(o|r|right)\.' 

Và một phép thử nhanh khi review: bỏ mệnh đề WHERE đi, đếm số dòng; thêm lại, đếm lại. Nếu số dòng giảm đúng bằng số dòng có NULL ở bảng phải, bạn vừa biến LEFT JOIN thành INNER JOIN.

---

Bài 2 — NOT IN gặp NULL trả về rỗng

Tạo một bảng có NULL trong cột tham chiếu và chạy NOT IN. Xác nhận kết quả rỗng. Chuyển sang NOT EXISTS và so sánh.

Tiêu chí hoàn thành: bạn giải thích được bằng logic ba trạng thái vì sao kết quả là rỗng hoàn toàn, chứ không phải thiếu vài dòng.

Gợi ý và lời giải — Bài 2

Gợi ý. x NOT IN (a, b, NULL) tương đương x <> a AND x <> b AND x <> NULL. Vế cuối cho giá trị gì?

Lời giải:

CREATE TABLE Users (Id INT PRIMARY KEY, Name NVARCHAR(100));
CREATE TABLE Leads (Id INT PRIMARY KEY, AssignedToUserId INT NULL);

INSERT INTO Users VALUES (1,N'An'),(2,N'Bình'),(3,N'Cường');
INSERT INTO Leads VALUES (1,1),(2,2),(3,NULL);        -- một lead CHƯA gán

-- Tìm nhân viên chưa được gán lead nào
SELECT * FROM Users
WHERE  Id NOT IN (SELECT AssignedToUserId FROM Leads);

(0 rows)

Rỗng hoàn toàn — dù Cường rõ ràng chưa được gán lead nào.

Giải thích bằng logic ba trạng thái. SQL không có hai giá trị logic mà có ba: TRUE, FALSE, UNKNOWN. Mọi phép so sánh với NULL đều cho UNKNOWN.

Tập con trả về (1, 2, NULL), nên điều kiện được khai triển thành:

Id NOT IN (1, 2, NULL)
  ≡  Id <> 1  AND  Id <> 2  AND  Id <> NULL

Xét Cường (Id = 3):

3 <> 1     -> TRUE
3 <> 2     -> TRUE
3 <> NULL  -> UNKNOWN
TRUE AND TRUE AND UNKNOWN  ->  UNKNOWN

WHERE chỉ giữ những dòng cho TRUE. UNKNOWN bị loại, y như FALSE.

Và đây là chỗ quan trọng: điều này đúng với MỌI dòng. Bất kể Id là gì, vế Id <> NULL luôn cho UNKNOWN, nên cả biểu thức không bao giờ đạt TRUE. Kết quả không phải "thiếu vài dòng" mà là rỗng hoàn toàn — chỉ cần một NULL duy nhất trong tập con.

Đó là lý do lỗi này đặc biệt khó chịu: nó không sai một chút, nó sai toàn bộ. Và trên dữ liệu thử không có NULL thì truy vấn chạy đúng hoàn hảo.

Với NOT EXISTS:

SELECT * FROM Users u
WHERE  NOT EXISTS (SELECT 1 FROM Leads l WHERE l.AssignedToUserId = u.Id);

Id  Name
3   Cường          <- ĐÚNG

Vì sao NOT EXISTS không bị ảnh hưởng. Nó không so sánh giá trị mà hỏi "có tồn tại dòng nào thoả không?". Với Cường, điều kiện l.AssignedToUserId = 3 cho UNKNOWN ở dòng có NULL và FALSE ở hai dòng kia — không dòng nào cho TRUE, nên EXISTS trả FALSE, và NOT EXISTS trả TRUE.

EXISTS chỉ có hai kết quả: có hoặc không. Nó không bao giờ trả UNKNOWN.

Bảng so sánh ba cách viết:

Cách viết	An toàn với NULL	Ghi chú
NOT IN	Không	Tránh, trừ khi chắc chắn không có NULL
NOT EXISTS	Có	Khuyến nghị
LEFT JOIN ... IS NULL	Có	Tương đương, đôi khi plan tốt hơn

-- Cách thứ ba
SELECT u.*
FROM   Users u
LEFT JOIN Leads l ON l.AssignedToUserId = u.Id
WHERE  l.Id IS NULL;

Ba cách phòng ngừa:

1. Đặt NOT NULL cho cột khi nghiệp vụ cho phép — vấn đề biến mất từ gốc.

2. Dùng NOT EXISTS làm mặc định, kể cả khi cột đang là NOT NULL. Ai đó có thể nới ràng buộc sau này, và truy vấn của bạn sẽ hỏng im lặng.

3. Lọc NULL trong tập con nếu buộc phải dùng NOT IN:

   WHERE Id NOT IN (SELECT AssignedToUserId FROM Leads WHERE AssignedToUserId IS NOT NULL)

Cùng logic ba trạng thái còn gây bất ngờ ở hai chỗ khác:

WHERE Value <> 100        -- LOẠI luôn dòng có Value = NULL
WHERE Value = NULL        -- không bao giờ TRUE, phải viết IS NULL

Dòng đầu là lỗi hay gặp nhất trong báo cáo: "lấy mọi đơn không phải 100" nhưng đơn chưa có giá trị bị bỏ qua.

---

Bài 3 — APPLY so với ROW_NUMBER cho bài toán "N mới nhất mỗi nhóm"

Với 50.000 khách hàng và 500.000 contact, cài "3 contact mới nhất mỗi khách hàng" bằng cả hai cách, so sánh thời gian có và không có index (CustomerId, CreatedAt DESC).

Tiêu chí hoàn thành: bạn nêu được vì sao index đổi thứ hạng giữa hai cách, chứ không chỉ làm cả hai nhanh lên.

Gợi ý và lời giải — Bài 3

Gợi ý. Một cách đọc mọi dòng rồi mới lọc; cách kia chỉ chạm đúng những dòng cần — nhưng chỉ khi có đường đi phù hợp.

Lời giải — hai cách viết:

-- Cách 1: ROW_NUMBER
WITH Ranked AS (
    SELECT c.*,
           ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.CreatedAt DESC) AS rn
    FROM   Contacts c
)
SELECT * FROM Ranked WHERE rn <= 3;

-- Cách 2: CROSS APPLY
SELECT cu.Id, cu.Name, ct.*
FROM   Customers cu
CROSS APPLY (
    SELECT TOP (3) *
    FROM   Contacts c
    WHERE  c.CustomerId = cu.Id
    ORDER  BY c.CreatedAt DESC
) ct;

Kết quả điển hình — KHÔNG có index:

	Thời gian	Logical reads
ROW_NUMBER	~1,9 giây	~3.400
CROSS APPLY	~14 giây	~420.000

ROW_NUMBER thắng rõ rệt.

Sau khi thêm index:

CREATE INDEX IX_Contacts_Customer_Created
    ON Contacts(CustomerId, CreatedAt DESC);

	Thời gian	Logical reads
ROW_NUMBER	~1,4 giây	~3.400
CROSS APPLY	~0,3 giây	~210.000

Thứ hạng đảo ngược.

Vì sao index đổi thứ hạng. Hai cách có hình dạng công việc khác nhau, và index chỉ giúp được một trong hai:

ROW_NUMBER luôn phải đọc toàn bộ bảng. Nó đánh số cho mọi dòng rồi mới lọc rn <= 3 ở bước sau. Với 500.000 contact, nó xử lý đủ 500.000 dòng dù bạn chỉ cần 150.000. Index giúp bỏ bước sắp xếp — nên nhanh lên một chút — nhưng khối lượng đọc không đổi.

CROSS APPLY chạy một truy vấn con cho mỗi khách hàng. Không có index, mỗi lần chạy là một lần quét bảng 500.000 dòng — 50.000 lần quét, nên con số 420.000 logical reads và 14 giây.

Có index (CustomerId, CreatedAt DESC) thì mỗi truy vấn con trở thành một index seek nhảy thẳng tới đúng khách hàng, rồi đọc đúng 3 dòng đầu theo thứ tự đã sắp sẵn. Tổng công việc là 50.000 × 3 = 150.000 dòng thay vì 500.000 — và không có bước sắp xếp nào.

Không index:  ROW_NUMBER đọc 500.000  <  APPLY đọc 50.000 × 500.000
Có index:     ROW_NUMBER đọc 500.000  >  APPLY đọc 50.000 × 3

Thứ tự DESC trong index là chi tiết quyết định. Với (CustomerId, CreatedAt) tăng dần, TOP (3) ... ORDER BY CreatedAt DESC phải đọc hết các dòng của khách hàng đó rồi đảo ngược. Với DESC, ba dòng đầu tiên đọc được chính là ba dòng cần.

Quy tắc chọn:

Tình huống	Nên dùng
Có index (GroupKey, SortKey DESC)	APPLY
Không có index và không thêm được	ROW_NUMBER
Cần N lớn (ví dụ 1.000 mỗi nhóm)	ROW_NUMBER
Số nhóm rất nhỏ	Khác biệt không đáng kể

PostgreSQL có cú pháp tương đương là LATERAL:

SELECT cu.id, cu.name, ct.*
FROM   customers cu
CROSS JOIN LATERAL (
    SELECT * FROM contacts c
    WHERE  c.customer_id = cu.id
    ORDER  BY c.created_at DESC
    LIMIT  3
) ct;

kèm index tương ứng:

CREATE INDEX idx_contacts_customer_created ON contacts(customer_id, created_at DESC);

Bài học rộng hơn bài tập này: so sánh hai cách viết truy vấn mà không nói rõ index nào đang có là so sánh vô nghĩa. Cùng một cặp truy vấn có thể đảo thứ hạng hoàn toàn chỉ vì một dòng CREATE INDEX — như bạn vừa thấy.

Tự kiểm tra

Câu hỏi thường gặp

Vì sao LEFT JOIN có thể biến thành INNER JOIN?

Khi bạn đặt điều kiện về bảng bên phải trong WHERE thay vì trong ON. Với dòng không khớp, cột bên phải là NULL và so sánh với NULL cho UNKNOWN nên dòng bị loại. Điều kiện về bảng bên phải thuộc về ON; chỉ điều kiện về bảng bên trái mới thuộc về WHERE.

Vì sao NOT IN với subquery có NULL lại trả về rỗng?

Vì NOT IN được diễn giải thành một chuỗi so sánh khác, và x khác NULL cho kết quả UNKNOWN chứ không phải TRUE, nên không dòng nào thoả. NOT EXISTS không có vấn đề này, và nên là lựa chọn mặc định.

CTE có vật chất hoá kết quả không?

Trong SQL Server thì thường không, nó được nội tuyến vào truy vấn chính như một subquery, nên CTE bị tham chiếu hai lần có thể thực thi hai lần. PostgreSQL trước phiên bản 12 thì luôn vật chất hoá; từ v12 nội tuyến theo mặc định và điều khiển được bằng từ khoá MATERIALIZED.

Window function khác GROUP BY ở đâu?

Window function giữ nguyên số dòng và thêm một cột tính trên nhóm, còn GROUP BY gộp các dòng lại. Nhờ đó bạn xem được từng dòng kèm thông tin tổng hợp của nhóm chứa nó.

Vì sao không lọc được theo kết quả của window function trong WHERE?

Vì window function được tính sau WHERE và GROUP BY trong thứ tự thực thi logic, nên tại thời điểm WHERE chạy thì cột đó chưa tồn tại. Cách chuẩn là bọc truy vấn trong CTE rồi lọc ở truy vấn ngoài; đây cũng là mẫu chuẩn cho bài toán top N mỗi nhóm.

Khi nào APPLY hay LATERAL nhanh hơn ROW_NUMBER?

Khi có nhiều nhóm và có index phù hợp, vì nó đi thẳng tới N dòng của mỗi nhóm thay vì đánh số toàn bộ bảng. Nhưng không có index thì nó thành một vòng lặp chạy một truy vấn cho mỗi dòng bên trái, đúng bản chất của vấn đề N+1.

Kết luận

Ba điều đáng nhớ nhất:

1. Điều kiện bảng bên phải thuộc về ON. Đặt vào WHERE là âm thầm mất dòng.
2. CTE là để dễ đọc, không nhanh hơn, và có thể chạy hai lần.
3. Window function chạy sau WHERE. Muốn lọc theo nó thì bọc trong CTE.

Tham khảo

• OVER clause (Transact-SQL)
• WITH common_table_expression (Transact-SQL)
• FROM clause plus JOIN, APPLY, PIVOT
• PostgreSQL — Window Functions

Điều hướng

• Bài trước: 12.2 — 2. SQL Server so với PostgreSQL
• Bài tiếp theo: 12.4 — 4. Indexing
• Về module: Trang mục lục

Bài liên quan

• Vì sao EF Core bắn 201 query cho 1 màn hình danh sách? Cách sửa N+1 — Một màn hình 200 dòng mà log SQL ghi 201 câu lệnh là dấu hiệu của N+1 query.