Khi nào dùng JOIN, khi nào dùng CTE hay window function · Chọn đúng công cụ SQL cho từng bài toán
Yêu cầu nghe thì đơn giản: lấy đơn hàng mới nhất của mỗi khách. Mình nhét một subquery vào WHERE, với mỗi đơn thì đi tìm created_at lớn nhất của khách đó. Data test chạy ngon. Lên bảng thật thì chậm hẳn, mà thỉnh thoảng một khách còn ra hai dòng.
Một anh trong team xem qua rồi viết lại bằng ROW_NUMBER(), ngắn hơn, hết luôn dòng trùng. Lúc đó mình mới ngộ ra: cú pháp JOIN, subquery, CTE, window function thì mình biết hết, nhưng khi nào dùng cái nào thì chưa bao giờ nghĩ cho ra nhẽ. Toàn viết theo quán tính.
Giờ trước khi viết query mình hay tự hỏi: rốt cuộc mình đang cần cái gì? Trả lời được câu đó thì công cụ gần như tự lộ ra.
Cần thêm cột từ bảng khác: JOIN
Đơn hàng kèm tên khách, sản phẩm kèm tên danh mục, mấy việc này là đất của JOIN. Cái mình luôn check trước là quan hệ giữa hai bảng. Nối từ phía nhiều sang phía một, ví dụ orders sang customers, thì số dòng giữ nguyên. Nối ngược lại thì khách có năm đơn sẽ thành năm dòng.
Dấu hiệu mình đang dùng sai: JOIN xong phải thêm DISTINCT thì kết quả mới "trông đúng". Gặp cái này gần như chắc là nên dùng thứ khác.
Chỉ cần biết có hay không: EXISTS
Ví dụ cần danh sách khách đã từng đặt hàng. Mình không cần cột nào của orders cả, chỉ cần biết khách đó có đơn hay không.
-- JOIN: khách có 5 đơn ra 5 dòng, phải DISTINCT để gộp lại
SELECT DISTINCT c.id, c.name
FROM customers AS c
INNER JOIN orders AS o
ON o.customer_id = c.id;
-- EXISTS: hỏi đúng cái mình muốn biết
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);
EXISTS không bao giờ làm tăng số dòng, và database có thể dừng ngay khi gặp đơn đầu tiên của khách. Chiều ngược lại, khách chưa từng đặt hàng, thì mình dùng NOT EXISTS chứ không dùng NOT IN. Lý do là NULL, chuyện này dài nên mình để sang bài về mấy lỗi SQL làm sai số liệu.
Cần so với một con số tổng hợp: subquery
Subquery hợp nhất khi nó trả về đúng một giá trị và không dính gì tới dòng bên ngoài:
SELECT id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);
Cái này tính một lần rồi dùng cho mọi dòng, không có gì phải lo. Thứ mình dè chừng là correlated subquery, tức subquery có tham chiếu tới dòng bên ngoài, nhất là khi đặt trong SELECT. Về mặt logic nó chạy lại cho từng dòng. Optimizer đôi khi tự viết lại thành JOIN giúp mình, nhưng mình không muốn hiệu năng phụ thuộc vào chuyện "đôi khi".
Query đầu bài của mình dính đúng cái này: với mỗi đơn, database lại đi tính lại ngày lớn nhất của khách đó.
Logic nhiều bước: CTE
Query cần hai ba bước nối nhau, kiểu lọc đơn hợp lệ, cộng tổng theo khách, rồi xếp hạng, thì mình viết mỗi bước thành một CTE có tên. Đọc từ trên xuống, và chạy riêng từng khối để check cũng dễ.
CTE còn có ích khi một kết quả trung gian được dùng hai lần trong cùng query. Không phải copy cùng một subquery ra hai chỗ, rồi sau này sửa một chỗ quên chỗ kia.
Một hiểu lầm mình từng có: tưởng chuyển subquery sang CTE thì query nhanh hơn. Không phải. Tuỳ database và phiên bản, CTE có thể được gộp vào query chính hoặc tính riêng ra một lần, cách nào cũng có lúc lợi lúc hại. Muốn biết thì xem execution plan. Còn CTE đệ quy để duyệt cây là chuyện khác, bài này không bàn.
Tính theo nhóm mà vẫn giữ từng dòng: window function
GROUP BY gộp nhiều dòng thành một. Window function cũng tính trên một nhóm dòng nhưng trả kết quả về cho từng dòng. Xếp hạng trong nhóm, cộng luỹ kế, so với dòng liền trước đều thuộc loại này.
Quay lại bài toán đơn mới nhất của mỗi khách:
WITH ranked AS (
SELECT
o.id,
o.customer_id,
o.total_amount,
o.created_at,
ROW_NUMBER() OVER (
PARTITION BY o.customer_id
ORDER BY o.created_at DESC, o.id DESC
) AS rn
FROM orders AS o
)
SELECT id, customer_id, total_amount, created_at
FROM ranked
WHERE rn = 1;
Có hai chỗ mình phải vấp mới nhớ.
Thứ nhất là o.id DESC ở cuối ORDER BY. Hai đơn của cùng một khách mà trùng created_at, nếu chỉ sắp theo created_at thì database chọn đơn nào cũng được, chạy hai lần có thể ra hai kết quả khác nhau. Thêm một cột để phân định là xong. Query cũ của mình ra hai dòng cho một khách cũng vì hai đơn trùng thời điểm kiểu này.
Thứ hai, không viết WHERE rn = 1 ngay trong cùng câu SELECT được, vì window function được tính sau WHERE. Phải bọc trong CTE hoặc subquery rồi lọc ở ngoài.
Thêm một ví dụ nữa, doanh thu luỹ kế theo ngày:
SELECT
order_date,
revenue,
SUM(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_revenue
FROM daily_revenue;
Mình ghi rõ ROWS BETWEEN ... chứ không để mặc định. Có ORDER BY thì frame mặc định là RANGE, tức các dòng cùng giá trị sắp xếp được cộng chung một lượt. Bảng có hai dòng cùng ngày là cả hai hiện cùng một số luỹ kế, nhìn y như bug dù không phải.
MySQL có window function từ 8.0. PostgreSQL thì còn có DISTINCT ON, giải bài đơn mới nhất mỗi nhóm còn ngắn hơn, có điều cú pháp này không mang sang database khác được.
Tóm lại thì
| Mình cần gì | Dùng | Để ý |
|---|---|---|
| Thêm cột từ bảng khác | JOIN | Quan hệ một–nhiều làm nhân dòng |
| Lọc theo có hay không có dữ liệu liên quan | EXISTS / NOT EXISTS | Không làm tăng số dòng |
| So với một giá trị tổng hợp | Subquery trả về một giá trị | Cẩn thận correlated subquery |
| Logic nhiều bước, dùng lại kết quả trung gian | CTE | Để dễ đọc, không phải để nhanh |
| Tính theo nhóm mà giữ từng dòng | Window function | Không lọc trực tiếp trong WHERE được |
Mấy câu hay bị hỏi:
JOIN hay subquery nhanh hơn? Không có câu trả lời chung. Nhiều khi optimizer biến cả hai thành cùng một plan. Mình viết cách nào nói đúng ý định trước, chậm thì đo sau.
Có nên thay mọi GROUP BY bằng window function? Không. Kết quả cuối chỉ cần một dòng mỗi nhóm thì GROUP BY rõ ràng hơn.
Bao nhiêu CTE là nhiều? Mình không đếm. Khi nào không đặt nổi một cái tên có nghĩa cho CTE, tức là bước đó chính mình cũng chưa rõ nó làm gì.
Thử ngay thì dễ: tìm trong project một query có SELECT DISTINCT đi kèm JOIN, viết lại bằng EXISTS, so kết quả. Bài sau mình nói về execution plan và index, tức là cách kiểm tra xem lựa chọn của mình chạy có thật sự ổn không.
P.S. Khi phân vân giữa hai cách viết, mình hay mô tả yêu cầu cho AI rồi nhờ nó viết cả hai bản và chỉ ra chỗ khác nhau về số dòng. Mình dùng skill database-schema-designer trong PrimeKit, bộ skill mình tự làm, vì nó có sẵn tài liệu về JOIN, CTE, window function cho PostgreSQL nên đỡ bị bịa cú pháp. Gõ /pk:database-schema-designer trong Claude Code là dùng được. Hai bản nó đưa ra thì mình vẫn tự EXPLAIN rồi mới chọn.
All Rights Reserved