Hoàn thành bài "Bài 1: Kiến thức nền tảng PostgreSQL và Thiết kế quan hệ" trước để mở khoá bài này.Đến bài trước

Giới thiệu

Khi làm việc với PostgreSQL trong các dự án backend thực tế, bạn sẽ hiếm khi chỉ truy vấn trên một bảng duy nhất. Dữ liệu thường được chuẩn hóa thành nhiều bảng liên kết với nhau qua khóa ngoại, và bạn cần kết hợp chúng lại để trả về thông tin có ý nghĩa. Bài học này tập trung vào ba kỹ thuật quan trọng giúp bạn viết các câu truy vấn phức tạp một cách rõ ràng và hiệu quả: JOIN để kết nối bảng, subquery để truy vấn lồng nhau, và CTE để cấu trúc hóa logic truy vấn.

Chúng ta sẽ dùng một schema mẫu mô phỏng hệ thống bán hàng gồm bốn bảng chính: customers, orders, products và order_items. Bảng customers lưu thông tin khách hàng, orders ghi nhận đơn hàng từ khách, products chứa danh mục sản phẩm, còn order_items là bảng trung gian lưu chi tiết từng sản phẩm trong mỗi đơn hàng. Đây là kiểu quan hệ một-nhiều lồng nhau mà bạn sẽ gặp thường xuyên.

JOIN giữa nhiều bảng

JOIN là cách phổ biến nhất để kết hợp dữ liệu từ nhiều bảng trong cùng một truy vấn. PostgreSQL hỗ trợ INNER JOIN (chỉ lấy bản ghi có khớp ở cả hai bảng), LEFT JOIN (lấy tất cả bản ghi bên trái kể cả khi không có khớp bên phải), và các biến thể khác như RIGHT JOIN, FULL OUTER JOIN. Trong thực tế backend, bạn sẽ dùng INNER JOIN và LEFT JOIN nhiều nhất.

Ví dụ dưới đây lấy danh sách đơn hàng kèm tên khách hàng và tổng số tiền:

sql
SELECT o.id AS order_id, c.name AS customer_name, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 10;

Khi cần join nhiều bảng cùng lúc, chỉ cần nối thêm các mệnh đề JOIN. Ví dụ lấy chi tiết từng sản phẩm trong đơn hàng gồm tên sản phẩm và số lượng:

sql
SELECT o.id AS order_id, p.name AS product_name, oi.quantity, oi.unit_price
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = 42;

Một lỗi phổ biến là join bảng mà quên điều kiện ON, dẫn đến tích Descartes và truy vấn chạy rất chậm. Luôn kiểm tra rằng mỗi JOIN có điều kiện khớp khóa rõ ràng.

sơ đồ quan hệ giữa 4 bảng customers, orders, order_items, products với các đường nối khóa ngoại

Subqueries và correlated subqueries

Subquery là một truy vấn SELECT lồng bên trong truy vấn khác. Bạn có thể dùng subquery trong mệnh đề WHERE để lọc, trong SELECT để tính toán giá trị, hoặc trong FROM để tạo bảng tạm. Một dạng đặc biệt là correlated subquery, trong đó truy vấn con tham chiếu đến cột của truy vấn ngoài, nghĩa là nó được thực thi lại cho mỗi hàng kết quả.

Ví dụ tìm khách hàng đã có ít nhất một đơn hàng bằng subquery đơn giản:

sql
SELECT name, email
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total_amount > 500);

Ví dụ correlated subquery đếm số đơn hàng cho từng khách hàng hiển thị trong danh sách:

sql
SELECT c.name, c.email,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c
WHERE c.created_at > '2024-01-01';

Truy vấn con trong cùng bên ngoài tham chiếu c.id, nên PostgreSQL phải chạy lại nó cho từng khách hàng. Với bảng lớn, correlated subquery có thể chậm, vì vậy hãy cân nhắc chuyển sang JOIN hoặc CTE nếu hiệu năng là vấn đề.

Common Table Expressions với WITH

CTE (Common Table Expression) cho phép bạn định nghĩa các truy vấn tạm thời có tên ngay trong câu lệnh, giúp logic phức tạp trở nên dễ đọc hơn. CTE bắt đầu bằng từ khóa WITH, theo sau là danh sách các truy vấn đặt tên, rồi đến truy vấn chính sử dụng chúng.

Ví dụ tính tổng doanh thu theo từng khách hàng trong năm 2024, sau đó lọc ra những người có doanh thu trên 1000:

sql
WITH customer_revenue AS (
SELECT o.customer_id, SUM(o.total_amount) AS revenue
FROM orders o
WHERE o.created_at >= '2024-01-01' AND o.created_at < '2025-01-01'
GROUP BY o.customer_id
)
SELECT c.name, cr.revenue
FROM customer_revenue cr
INNER JOIN customers c ON c.id = cr.customer_id
WHERE cr.revenue > 1000
ORDER BY cr.revenue DESC;

CTE còn hỗ trợ dạng đệ quy (recursive CTE) với từ khóa WITH RECURSIVE, rất hữu ích cho việc duyệt cây phân cấp như cấu trúc tổ chức công ty hoặc danh mục sản phẩm đa cấp. Ngoài ra, PostgreSQL 12 trở lên cho phép dùng vật chất hóa (materialized) với WITH ... AS MATERIALIZED để tăng tốc truy vấn trong một số trường hợp.

sơ đồ luồng truy vấn CTE 4 bước, từ khai báo bảng tạm đến lọc và sắp xếp kết quả

Lỗi thường gặp và mẹo thực chiến

Một lỗi phổ biến là nhầm lẫn giữa JOIN và subquery trong mệnh đề WHERE dùng IN hoặc EXISTS. Cả hai đều cho kết quả tương đương trong nhiều tình huống, nhưng EXISTS thường nhanh hơn khi bạn chỉ cần kiểm tra sự tồn tại thay vì lấy giá trị. Ví dụ tìm khách hàng đã từng đặt hàng:

sql
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Khi viết CTE, hãy đặt tên có ý nghĩa như customer_revenue hay top_products để người đọc dễ hiểu mục đích của từng bước. Tránh lạm dụng CTE khi một JOIN đơn giản đủ dùng, vì CTE có thể tốn thêm chi phí tối ưu hóa trong một số phiên bản PostgreSQL. Cuối cùng, luôn kiểm tra kế hoạch thực thi bằng EXPLAIN ANALYZE khi truy vấn phức tạp chạy chậm, vì đôi khi correlated subquery có thể được thay bằng JOIN để tăng tốc đáng kể.

Tổng kết

Trong bài này bạn đã học cách kết hợp nhiều bảng bằng JOIN, viết subquery và correlated subquery để lọc và tính toán, cũng như dùng CTE với WITH để cấu trúc truy vấn phức tạp thành các khối dễ đọc. Ba kỹ thuật này là nền tảng để bạn xử lý hầu hết các yêu cầu truy vấn trong ứng dụng backend thực tế, từ việc lấy danh sách đơn hàng kèm chi tiết cho đến tính toán báo cáo tổng hợp. Ở bài tiếp theo, bạn sẽ tìm hiểu thêm về indexing và cách tối ưu hiệu năng cho các truy vấn phức tạp mà bạn vừa viết.

Luyện tập sau buổi học

Bài kiểm tra cuối bài

Ôn lại kiến thức, thử sức và nhận góp ý cho bài làm.

0/8
01Sự khác biệt chính giữa INNER JOIN và LEFT JOIN trong PostgreSQL là gì?
02Điều gì xảy ra nếu bạn viết mệnh đề JOIN mà không có điều kiện ON?
03Trong một truy vấn con tương quan, truy vấn bên trong tham chiếu đến điều gì?
04Lợi ích chính của việc sử dụng CTE (mệnh đề WITH) trong PostgreSQL là gì?
05Từ khóa nào là bắt buộc để định nghĩa một CTE đệ quy nhằm duyệt qua dữ liệu phân cấp?
06Khi nào nên ưu tiên dùng EXISTS thay cho IN trong một truy vấn con?
07Theo bài học, bạn nên làm gì khi một truy vấn phức tạp chạy chậm?
08Truy vấn CTE nào tính đúng tổng doanh thu theo khách hàng và lọc những khách hàng có doanh thu trên 1000?
Đã trả lời · 0/8