ldhnam
Công nghệ, kinh tế, và những suy nghĩ rời rạc.Technology, economics, and scattered thoughts.
← PostgreSQL DBA← PostgreSQL DBA
PostgreSQL DBAPostgreSQL DBA19 Th7, 2026Jul 19, 202628 phút đọc23 min read

Truy vấn nâng caoAdvanced Querying

Thuộc bộ kiến thức PostgreSQL DBA Roadmap.

Tổng quan

Các câu lệnh SELECT ... WHERE ... JOIN cơ bản đã giải quyết được nhiều bài toán, nhưng báo cáo, phân tích dữ liệu, và các bài toán dữ liệu phân cấp trong thực tế đòi hỏi một bộ công cụ phong phú hơn. Bài viết này trình bày các cấu trúc phân biệt người “biết viết query” với người có thể diễn đạt logic nghiệp vụ phức tạp một cách khai báo (declarative) và hiệu quả bằng SQL: set operations, subquery, Common Table Expression (CTE), recursive CTE, LATERAL join, và window function.

Đây không chỉ là cú pháp cho đẹp. Chọn đúng công cụ có ảnh hưởng thực sự đến hiệu năng — một subquery dùng IN và một subquery EXISTS tương đương về ngữ nghĩa có thể tạo ra hai query plan rất khác nhau, và CTE trong PostgreSQL trước phiên bản 12 từng là rào cản tối ưu hóa (optimization barrier) cứng, điều khiến không ít developer quen với database khác phải bất ngờ. Hiểu rõ những chi tiết này giúp bạn viết ra query vừa dễ đọc vừa nhanh, đồng thời chuẩn bị nền tảng cho chủ đề tiếp theo trong loạt bài này — query planning và performance tuning — nơi ta xem xét PostgreSQL thực thi các cấu trúc này như thế nào ở tầng dưới.

Bài viết giả định bạn đã quen với SQL cơ bản như trong 04 — Querying Data / SQL Fundamentals. Về cách planner biến các query này thành execution plan (và cách đọc output của EXPLAIN cho chúng), xem 10 — Query Planning and Performance Tuning.

Xuyên suốt bài viết, ta dùng schema mẫu sau:

CREATE TABLE customers (
    customer_id serial PRIMARY KEY,
    name         text NOT NULL,
    country      text NOT NULL
);

CREATE TABLE orders (
    order_id     serial PRIMARY KEY,
    customer_id  int NOT NULL REFERENCES customers(customer_id),
    order_date   date NOT NULL,
    amount       numeric(10,2) NOT NULL
);

CREATE TABLE employees (
    employee_id   int PRIMARY KEY,
    name          text NOT NULL,
    manager_id    int REFERENCES employees(employee_id),
    department    text NOT NULL,
    salary        numeric(10,2) NOT NULL
);

Kiến thức nền tảng

Set operations: UNION, INTERSECT, EXCEPT

Set operation kết hợp kết quả của hai hay nhiều câu SELECT có cùng số cột và kiểu dữ liệu tương thích. PostgreSQL hỗ trợ ba toán tử set, mỗi toán tử có biến thể “distinct” (mặc định) và “all”.

UNION gộp các dòng từ hai query và loại bỏ trùng lặp theo mặc định:

SELECT name, country FROM customers WHERE country = 'Vietnam'
UNION
SELECT name, country FROM customers WHERE country = 'Singapore';

UNION ALL làm điều tương tự nhưng giữ lại các dòng trùng lặp:

SELECT name, country FROM customers WHERE country = 'Vietnam'
UNION ALL
SELECT name, country FROM customers WHERE country = 'Singapore';

Lý do UNION ALL nhanh hơn UNION không phải là một tối ưu vặt vãnh — đó là một sự khác biệt căn bản về thuật toán. UNION phải sort hoặc hash toàn bộ tập kết quả gộp để xác định và loại bỏ các dòng trùng lặp, tức là thêm một lượt duyệt toàn bộ dữ liệu (thể hiện trong EXPLAIN bằng node HashAggregate hoặc Unique phía trên Append). UNION ALL chỉ đơn giản nối các tập dòng lại bằng node Append và không thực hiện bất kỳ công việc khử trùng lặp nào. Nếu bạn đã biết chắc hai query không thể tạo ra dòng trùng lặp (như ví dụ trên, khi country loại trừ lẫn nhau), hãy luôn ưu tiên UNION ALL — kết quả đúng vẫn giống hệt, nhưng bạn tiết kiệm được một lượt xử lý tốn kém.

INTERSECT chỉ trả về các dòng xuất hiện ở cả hai tập kết quả:

-- Khách hàng đặt đơn cả trong năm 2023 VÀ 2024
SELECT customer_id FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
INTERSECT
SELECT customer_id FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

Cách này hữu ích cho phân tích kiểu “thuộc cả hai nhóm” — ví dụ, tìm khách hàng hoạt động trong hai giai đoạn khác nhau, hoặc sản phẩm xuất hiện trong hai catalog khác nhau.

EXCEPT trả về các dòng từ query đầu tiên mà không xuất hiện trong query thứ hai (phép hiệu tập hợp, thứ tự có ý nghĩa):

-- Khách hàng đặt đơn năm 2023 nhưng KHÔNG đặt đơn năm 2024 (churn)
SELECT customer_id FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
EXCEPT
SELECT customer_id FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

EXCEPT là công cụ tự nhiên cho việc “tìm cái đang thiếu” hoặc phân tích churn: các dòng có mặt trong tập tham chiếu nhưng vắng mặt trong tập so sánh. Cả ba toán tử đều có biến thể ALL (INTERSECT ALL, EXCEPT ALL) dùng ngữ nghĩa multiset (so khớp số lượng bản sao) thay vì khử trùng lặp, tuy nhiên trong thực tế ít được dùng hơn nhiều.

Subquery

Subquery là một câu SELECT được lồng bên trong một câu lệnh khác. Subquery có vài dạng khác nhau, ảnh hưởng đến cả khả năng đọc hiểu lẫn hiệu năng.

Scalar subquery trả về đúng một dòng và một cột, có thể dùng ở bất cứ đâu cần một giá trị đơn:

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

Nếu một scalar subquery bất ngờ trả về nhiều hơn một dòng lúc chạy, PostgreSQL sẽ báo lỗi (more than one row returned by a subquery used as an expression), vì vậy chỉ nên dùng khi bạn đảm bảo được tối đa một dòng khớp.

Subquery trong mệnh đề WHERE với IN kiểm tra tư cách thành viên trong một tập:

SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 1000);

Đây là một uncorrelated subquery (subquery không tương quan) — query bên trong không tham chiếu bất cứ thứ gì từ query bên ngoài, nên về nguyên tắc PostgreSQL có thể thực thi nó một lần và tái sử dụng kết quả.

Correlated subquery (subquery tương quan) tham chiếu một cột từ query bên ngoài bên trong query bên trong, do đó về mặt logic nó được đánh giá lại (hoặc ít nhất được xem là đánh giá lại) cho từng dòng bên ngoài:

-- Khách hàng có đơn hàng gần nhất cao hơn mức trung bình lịch sử của chính họ
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.amount > (
          SELECT AVG(o2.amount) FROM orders o2 WHERE o2.customer_id = c.customer_id
      )
);

Ở đây, subquery trong cùng tương quan với c.customer_id từ query ngoài cùng, và subquery EXISTS ở giữa cũng tương quan với c.customer_id. Về mặt logic, mỗi subquery được đánh giá lại cho từng khách hàng ứng viên, dù planner có thể viết lại thành một dạng kế hoạch giống join ở bên trong.

IN so với EXISTS so với NOT EXISTS

IN và EXISTS thường trông có thể thay thế lẫn nhau, nhưng chúng hoạt động khác nhau, đặc biệt khi dữ liệu lớn:

-- Dùng IN
SELECT name FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o WHERE o.amount > 500);

-- Dùng EXISTS (correlated)
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id AND o.amount > 500
);

Cả hai query trả về cùng kết quả, nhưng chiến lược thực thi khác nhau. Về mặt khái niệm, IN cần toàn bộ danh sách giá trị khớp từ subquery trước khi có thể kiểm tra tư cách thành viên; trước đây, và trong một số dạng plan hiện nay vẫn vậy, điều này đồng nghĩa với việc phải materialize (hoặc khử trùng lặp) toàn bộ kết quả subquery trước khi dò cho từng dòng bên ngoài. Ngược lại, EXISTS là một phép kiểm tra tồn tại: PostgreSQL có thể dừng quét query bên trong ngay khi tìm thấy một dòng khớp cho dòng ngoài hiện tại (một “semi-join” có hành vi short-circuit), thay vì phải tính toàn bộ tập kết quả bên trong.

Trong PostgreSQL hiện đại (9.x trở lên), planner thường đủ thông minh để viết lại subquery IN thành một semi-join plan có hiệu năng tương đương EXISTS, đặc biệt khi subquery chỉ là một phép so sánh bằng đơn giản, có index. Nhưng việc viết lại này không được đảm bảo trong mọi trường hợp — nó phụ thuộc vào hình dạng subquery, mức độ tương quan, và việc planner có nhận ra đây là biến đổi an toàn hay không. EXISTS cho bạn một đảm bảo mạnh hơn về hành vi short-circuit và thường ổn định hơn khi:

-- Khách hàng chưa từng đặt đơn hàng nào
-- NOT EXISTS: an toàn, luôn đúng
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- NOT IN: nguy hiểm nếu orders.customer_id có thể là NULL
SELECT c.name FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o);  -- trả về 0 dòng nếu có bất kỳ o.customer_id nào IS NULL

Quy tắc thực tế: ưu tiên EXISTS/NOT EXISTS cho các kiểm tra tồn tại tương quan và bất cứ khi nào tính an toàn với NULL quan trọng, và đừng mặc định IN luôn chậm hơn — luôn kiểm tra bằng EXPLAIN ANALYZE trên khối lượng dữ liệu thực tế của bạn (xem 10 — Query Planning and Performance Tuning).

Khái niệm chính

Common Table Expression (CTE)

CTE, được khai báo bằng mệnh đề WITH, cho phép bạn đặt tên cho một subquery và tham chiếu lại nó sau đó trong câu lệnh như thể đó là một bảng. Lợi ích chính là khả năng đọc hiểu: thay vì lồng chín cấp subquery, bạn chia một query phức tạp thành các bước logic có tên, tuần tự.

WITH high_value_customers AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) > 5000
),
recent_orders AS (
    SELECT customer_id, order_id, order_date, amount
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
),
combined AS (
    SELECT hvc.customer_id, hvc.total_spent, ro.order_id, ro.order_date, ro.amount
    FROM high_value_customers hvc
    JOIN recent_orders ro ON ro.customer_id = hvc.customer_id
)
SELECT c.name, combined.total_spent, combined.order_id, combined.order_date, combined.amount
FROM combined
JOIN customers c ON c.customer_id = combined.customer_id
ORDER BY combined.total_spent DESC, combined.order_date DESC;

Mỗi khối WITH đọc như một bước trong một pipeline: đầu tiên tìm khách hàng giá trị cao, sau đó tìm đơn hàng gần đây, rồi join chúng lại, cuối cùng bổ sung tên khách hàng. Điều này dễ review và chỉnh sửa hơn rất nhiều so với một query tương đương lồng chín cấp dấu ngoặc, và mỗi CTE có tên có thể được kiểm tra độc lập bằng cách chạy nó như một SELECT đứng riêng trong lúc phát triển query.

Rào cản tối ưu hóa CTE của PostgreSQL (materialization) — một chi tiết đặc thù đáng lưu ý

Đây là một trong những chi tiết đặc thù của PostgreSQL quan trọng nhất đối với ai đến từ một RDBMS khác, và nó đã thay đổi đáng kể qua các phiên bản:

-- PostgreSQL 12+: MATERIALIZED ép một rào cản tối ưu hóa cứng, giống hệt hành vi mặc định trước phiên bản 12
WITH big_cte AS MATERIALIZED (
    SELECT * FROM orders
)
SELECT * FROM big_cte WHERE customer_id = 42;

-- PostgreSQL 12+: NOT MATERIALIZED ép inline ngay cả khi planner muốn materialize
WITH filtered AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM filtered WHERE customer_id = 42;

MATERIALIZED vẫn thực sự hữu ích — ví dụ, khi một CTE tốn kém để tính toán và được tham chiếu nhiều lần trong query ngoài, ép materialize giúp tránh tính lại nó, hoặc khi CTE có các hàm không ổn định (volatile) (ví dụ random(), các hàm tiêu thụ sequence) mà kết quả cần phải giữ nguyên qua tất cả các lần tham chiếu. CTE đệ quy (bên dưới) luôn được materialize bất kể phiên bản nào, vì mô hình đánh giá đệ quy đòi hỏi như vậy.

Điểm cần nhớ cho bất kỳ ai đang audit hoặc viết query trên PostgreSQL production: nếu một query đang chạy trên PostgreSQL 11 trở về trước và sử dụng CTE nhiều kèm điều kiện lọc, hãy kiểm tra xem viết lại thành subquery (hoặc nâng cấp phiên bản) có cải thiện plan không, và luôn xác nhận hành vi thực tế bằng EXPLAIN (ANALYZE, BUFFERS) thay vì chỉ dựa vào phiên bản.

Recursive CTE

WITH RECURSIVE là cơ chế của PostgreSQL để duyệt dữ liệu phân cấp hoặc dạng đồ thị: sơ đồ tổ chức (org chart), cây danh mục (category tree), bảng kê vật tư (bill-of-materials), đồ thị phụ thuộc, và tìm đường đi. Một recursive CTE có hai phần, kết hợp bằng UNION hoặc UNION ALL:

  1. Base case (anchor member): một câu SELECT không đệ quy, tạo ra (các) dòng khởi đầu.
  2. Recursive case (recursive member): một câu SELECT tham chiếu đến chính tên của CTE, join kết quả của lần lặp trước để tạo ra cấp tiếp theo. PostgreSQL chạy lặp lại phần này, đưa kết quả của mỗi lần lặp quay lại làm đầu vào, cho đến khi recursive member trả về 0 dòng.

Dưới đây là một ví dụ đầy đủ về việc duyệt sơ đồ phân cấp nhân viên-quản lý, tìm tất cả những người báo cáo, trực tiếp hoặc gián tiếp, cho một quản lý cho trước, cùng với độ sâu của họ trong sơ đồ tổ chức:

WITH RECURSIVE org_chart AS (
    -- Base case: quản lý ta bắt đầu từ đó (độ sâu 0)
    SELECT employee_id, name, manager_id, department, 0 AS depth,
           name::text AS path
    FROM employees
    WHERE employee_id = 1  -- ví dụ: CEO

    UNION ALL

    -- Recursive case: tìm cấp dưới trực tiếp của những người đã có trong org_chart
    SELECT e.employee_id, e.name, e.manager_id, e.department, oc.depth + 1,
           oc.path || ' -> ' || e.name
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT employee_id, name, department, depth, path
FROM org_chart
ORDER BY depth, name;

Xét qua cách thực thi: PostgreSQL trước tiên chạy anchor member và đưa các dòng của nó vào một bảng làm việc (về mặt khái niệm, là nhân viên có id 1). Sau đó, nó chạy lặp lại recursive member, nhưng mỗi lần lặp chỉ “thấy” các dòng được thêm vào ở lần lặp trước đó dưới tên org_chart (không phải toàn bộ kết quả tích lũy) — vì vậy lượt đệ quy đầu tiên tìm cấp dưới trực tiếp của CEO, lượt thứ hai tìm cấp dưới trực tiếp của những người đó, cứ thế tiếp tục. Các dòng mới của mỗi lần lặp được thêm vào kết quả cuối cùng và đưa trở lại làm đầu vào, và quá trình tự động dừng lại khi một lần lặp không tạo ra dòng mới nào (ví dụ, khi đến những nhân viên không có cấp dưới trực tiếp).

Một vài lưu ý thực tế về recursive CTE:

WITH RECURSIVE org_chart AS (
    SELECT employee_id, manager_id, ARRAY[employee_id] AS visited, 0 AS depth
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    SELECT e.employee_id, e.manager_id, oc.visited || e.employee_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
    WHERE NOT e.employee_id = ANY(oc.visited)  -- bảo vệ chống chu trình
)
SELECT * FROM org_chart;

LATERAL join

Một JOIN thông thường không thể có vế phải tham chiếu đến các cột của vế trái — mỗi vế của một join thông thường được đánh giá độc lập, và chỉ được kết hợp sau đó thông qua điều kiện join. LATERAL (ngầm định đối với hàm, tường minh qua từ khóa LATERAL đối với subquery) thay đổi điều này: nó cho phép subquery hoặc hàm ở vế phải tham chiếu đến các cột của các bảng xuất hiện trước đó trong cùng mệnh đề FROM, hiệu quả là chạy vế phải một lần cho mỗi dòng của vế trái, giống như một correlated subquery được nhúng trực tiếp trong mệnh đề FROM thay vì mệnh đề WHERE.

Ví dụ điển hình mà tính năng này mở khóa là “top N mỗi nhóm” — ví dụ, 3 đơn hàng gần nhất của mỗi khách hàng:

SELECT c.name, top_orders.order_id, top_orders.order_date, top_orders.amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.order_id, o.order_date, o.amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
    LIMIT 3
) AS top_orders
ORDER BY c.name, top_orders.order_date DESC;

Chú ý rằng subquery tham chiếu c.customer_id — chính tham chiếu cột đó là điều làm cho query này là LATERAL; nếu không có từ khóa này, PostgreSQL sẽ từ chối query với lỗi vì một subquery thông thường trong FROM hoàn toàn không thể “thấy” c. LIMIT 3 bên trong lateral subquery là theo từng khách hàng, đó chính là mấu chốt.

Vì sao mẫu này khó diễn đạt nếu không có LATERAL? Một window function như ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) cũng có thể giải quyết “top 3 mỗi nhóm,” nhưng nó đòi hỏi tính row number trên toàn bộ bảng orders rồi lọc lại ở query ngoài (WHERE rn <= 3), vì window function không thể xuất hiện trực tiếp trong mệnh đề WHERE. Điều đó thường vẫn ổn, nhưng có nghĩa là phải quét và xếp hạng mọi dòng trong orders, kể cả những khách hàng bạn không quan tâm, và trở nên bất tiện khi logic “top N” cần thêm tính toán theo từng dòng (ví dụ, gọi một hàm trả về tập hợp — set-returning function, hoặc cần một LIMIT phụ thuộc vào một giá trị theo nhóm, như “top N với N được lưu trong cột customers.vip_tier”). Một join thông thường (không phải lateral) hoàn toàn không thể làm điều này — không có cách nào để nói “join mỗi khách hàng với một lát cắt đã lọc, sắp xếp, giới hạn của chính đơn hàng của họ” chỉ bằng JOIN ... ON, vì điều kiện ON chỉ liên kết các dòng khớp nhau, nó không thể kiểm soát thứ tự hay giới hạn số dòng bên trong join. LATERAL là công cụ trực tiếp, có thể kết hợp, cho “với mỗi dòng bên trái, tính một kết quả tương quan, có thể giới hạn, có thể trả về tập hợp ở bên phải,” bao gồm cả việc gọi hàm trả về bảng (table-returning function) cho từng dòng, điều mà window function hoàn toàn không thể làm.

Aggregate function so với window function

Cả aggregate function và window function đều tính toán những thứ như tổng, đếm, trung bình trên các tập dòng, nhưng chúng khác nhau căn bản ở chỗ chúng làm gì với hình dạng của tập kết quả.

Aggregate function dùng cùng GROUP BY sẽ gộp mỗi nhóm dòng đầu vào thành một dòng đầu ra duy nhất:

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

Nếu orders có một nghìn dòng trải trên năm mươi khách hàng, query này trả về đúng năm mươi dòng — mỗi khách hàng một dòng — và các dòng đơn hàng riêng lẻ biến mất khỏi kết quả.

Window function, dùng mệnh đề OVER (...), tính một giá trị giống aggregate trên một tập các dòng liên quan (gọi là “window”) nhưng trả về một dòng đầu ra cho mỗi dòng đầu vào, giữ nguyên tất cả các cột gốc:

SELECT order_id, customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
       COUNT(*) OVER (PARTITION BY customer_id) AS customer_order_count
FROM orders;

Nếu orders có một nghìn dòng, query này vẫn trả về một nghìn dòng — mỗi dòng đơn hàng giờ cũng mang theo tổng chi tiêu và số đơn hàng của khách hàng đó, được tính mà không gộp bất cứ thứ gì lại.

Cấu trúc của mệnh đề OVER

Mệnh đề window có ba phần tùy chọn:

Các window function thường dùng

ROW_NUMBER() gán một số nguyên tăng chặt, duy nhất trong mỗi partition, không có trường hợp bằng nhau:

SELECT employee_id, name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

RANK() và DENSE_RANK() xử lý các trường hợp bằng nhau khác nhau. RANK() cho các dòng bằng nhau cùng một hạng nhưng để lại khoảng trống sau đó (1, 2, 2, 4); DENSE_RANK() cho các dòng bằng nhau cùng một hạng không có khoảng trống (1, 2, 2, 3):

SELECT employee_id, name, department, salary,
       RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_with_gaps,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_no_gaps
FROM employees;

LAG() và LEAD() nhìn vào một dòng trước hoặc sau dòng hiện tại trong window đã sắp xếp, mà không cần self-join:

SELECT customer_id, order_id, order_date, amount,
       LAG(amount)  OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_amount,
       LEAD(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_amount,
       amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS change_from_prev
FROM orders;

Ví dụ thực hành: running total và rank trong từng nhóm

Ví dụ này kết hợp một running total (tổng lũy kế theo ngày, cho từng khách hàng) với một rank của nhân viên theo lương trong phòng ban của họ, trình bày cả hai mẫu cạnh nhau:

-- Tổng lũy kế số tiền đơn hàng theo từng khách hàng, sắp theo ngày
SELECT customer_id, order_id, order_date, amount,
       SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY customer_id, order_date;

-- Xếp hạng lương của mỗi nhân viên trong phòng ban của họ
SELECT employee_id, name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY department, salary_rank;

Trong query đầu tiên, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW định nghĩa tường minh frame là “mọi dòng từ đầu partition của khách hàng này cho đến và bao gồm dòng hiện tại” — thực ra đây chính là frame mặc định khi có ORDER BY mà không có mệnh đề frame tường minh, nhưng viết ra tường minh giúp ý định rõ ràng, không mơ hồ. Trong query thứ hai, RANK() trả lời câu hỏi “lương của nhân viên này xếp hạng thế nào so với đồng nghiệp cùng phòng ban,” một câu hỏi mà GROUP BY đơn thuần không thể trả lời, vì GROUP BY sẽ gộp mất các dòng nhân viên riêng lẻ.

Window function cũng có thể kết hợp với CTE để lọc trên giá trị đã tính, vì window function không thể xuất hiện trực tiếp trong WHERE:

WITH ranked_employees AS (
    SELECT employee_id, name, department, salary,
           RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT * FROM ranked_employees WHERE salary_rank <= 2;  -- top 2 người lương cao nhất mỗi phòng ban

Bảng so sánh

CTE so với subquery so với LATERAL join

Khía cạnhCTE (WITH)SubqueryLATERAL join
Khả năng đọc hiểuCao — các bước có tên, tuần tựThấp hơn — lồng nhau che khuất logicTrung bình — tương quan tường minh, hiển thị trong FROM
Tái sử dụng trong cùng queryCó, tham chiếu tên CTE nhiều lầnKhông, phải lặp lại văn bản subquery (hoặc planner có thể đánh giá lại nó)Không, được đánh giá theo từng dòng bên trái, không tái sử dụng qua các dòng
Có thể tham chiếu cột của query ngoài (tương quan)Không (CTE không đệ quy được đánh giá độc lập; CTE đệ quy tự tham chiếu chính nó, không phải query ngoài)Có, nếu viết dưới dạng correlated subqueryCó — đây chính là đặc điểm định nghĩa của nó
Hành vi tối ưu hóaPG 12+: inline mặc định như subquery trừ khi có MATERIALIZED; trước 12: luôn là rào cảnThường được inline/gộp vào plan bởi plannerĐánh giá theo từng dòng ngoài; không phải rào cản, nhưng vốn tốn kém theo từng dòng
Trường hợp dùng điển hìnhChia một query phức tạp thành các bước logic có tên; duyệt đệ quyTra cứu giá trị đơn (scalar), kiểm tra tư cách thành viên IN/EXISTS”Top N mỗi nhóm,” gọi hàm trả về tập hợp cho từng dòng, LIMIT theo từng dòng

Aggregate function so với window function

Khía cạnhAggregate function (kèm GROUP BY)Window function (OVER (...))
Số dòng vào so với raNhiều dòng vào, một dòng ra mỗi nhómNhiều dòng vào, số dòng ra bằng nhau
Gộp mất các dòng chi tiếtCóKhông — các cột ở mức dòng gốc được giữ nguyên
Có thể dùng trong WHEREKhông (phải dùng HAVING để lọc sau khi aggregate)Không (phải bọc trong CTE/subquery rồi lọc ở query ngoài)
Trường hợp dùng điển hìnhTổng/tóm tắt theo từng nhóm (“tổng chi tiêu mỗi khách hàng”)Running total, xếp hạng, so sánh với dòng lân cận, ngữ cảnh theo từng dòng cùng với dữ liệu chi tiết

Best Practices

Tài liệu tham khảo

Part of the PostgreSQL DBA Roadmap knowledge base.

Overview

Basic SELECT ... WHERE ... JOIN statements take you a long way, but real-world reporting, analytics, and hierarchical data problems demand a richer toolbox. This note covers the constructs that separate someone who can “write a query” from someone who can express complex business logic declaratively and efficiently in SQL: set operations, subqueries, Common Table Expressions (CTEs), recursive CTEs, LATERAL joins, and window functions.

These features are not just syntactic sugar. Choosing the right tool has real performance consequences — an IN subquery and a semantically equivalent EXISTS subquery can produce very different query plans, and pre-12 PostgreSQL CTEs behaved as hard optimization barriers, which surprised many developers coming from other databases. Understanding these details lets you write queries that are both readable and fast, and it sets up the next topic in this series, query planning and performance tuning, where we look at how PostgreSQL executes these constructs under the hood.

This note assumes familiarity with basic SQL as covered in 04 — Querying Data / SQL Fundamentals. For how the planner turns these queries into execution plans (and how to read EXPLAIN output for them), see 10 — Query Planning and Performance Tuning.

Throughout this note we use a small sample schema:

CREATE TABLE customers (
    customer_id serial PRIMARY KEY,
    name         text NOT NULL,
    country      text NOT NULL
);

CREATE TABLE orders (
    order_id     serial PRIMARY KEY,
    customer_id  int NOT NULL REFERENCES customers(customer_id),
    order_date   date NOT NULL,
    amount       numeric(10,2) NOT NULL
);

CREATE TABLE employees (
    employee_id   int PRIMARY KEY,
    name          text NOT NULL,
    manager_id    int REFERENCES employees(employee_id),
    department    text NOT NULL,
    salary        numeric(10,2) NOT NULL
);

Fundamentals

Set operations: UNION, INTERSECT, EXCEPT

Set operations combine the results of two or more SELECT statements that have the same number of columns with compatible types. PostgreSQL supports three set operators, each with a “distinct” (default) and an “all” variant.

UNION combines rows from two queries and removes duplicates by default:

SELECT name, country FROM customers WHERE country = 'Vietnam'
UNION
SELECT name, country FROM customers WHERE country = 'Singapore';

UNION ALL does the same thing but keeps duplicates:

SELECT name, country FROM customers WHERE country = 'Vietnam'
UNION ALL
SELECT name, country FROM customers WHERE country = 'Singapore';

The reason UNION ALL is faster than UNION is not a micro-optimization — it is a fundamentally different algorithm. UNION must sort or hash the combined result set to identify and eliminate duplicate rows, an extra full pass over the data (visible in EXPLAIN as a HashAggregate or Unique node on top of an Append). UNION ALL simply concatenates the row sets with an Append node and does no deduplication work at all. If you already know the two queries cannot produce overlapping rows (as in the example above, where country is mutually exclusive), always prefer UNION ALL — the correctness is identical, but you skip an expensive pass.

INTERSECT returns only rows that appear in both result sets:

-- Customers who ordered in 2023 AND 2024
SELECT customer_id FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
INTERSECT
SELECT customer_id FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

This is useful for “in both groups” analysis — for example, finding customers who were active in two different periods, or products that appear in two different catalogs.

EXCEPT returns rows from the first query that do not appear in the second (set difference, order matters):

-- Customers who ordered in 2023 but did NOT order in 2024 (churned)
SELECT customer_id FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
EXCEPT
SELECT customer_id FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

EXCEPT is the natural tool for “find what’s missing” or churn-style analysis: rows present in a reference set but absent from a comparison set. All three operators also have ALL variants (INTERSECT ALL, EXCEPT ALL) that use multiset semantics (matching duplicate counts) rather than deduplicating, though these are used far less often in practice.

Subqueries

A subquery is a SELECT nested inside another statement. Subqueries come in a few flavors that matter for both readability and performance.

A scalar subquery returns exactly one row and one column, and can be used anywhere a single value is expected:

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

If a scalar subquery unexpectedly returns more than one row at runtime, PostgreSQL raises an error (more than one row returned by a subquery used as an expression), so use them only when you can guarantee at most one matching row.

A subquery in the WHERE clause with IN checks membership against a set:

SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 1000);

This is an uncorrelated subquery — the inner query does not reference anything from the outer query, so PostgreSQL can, in principle, execute it once and reuse the result.

A correlated subquery references a column from the outer query inside the inner query, so it conceptually re-runs (or is at least logically evaluated) once per outer row:

-- Customers whose most recent order is above their own historical average
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.amount > (
          SELECT AVG(o2.amount) FROM orders o2 WHERE o2.customer_id = c.customer_id
      )
);

Here, the innermost subquery is correlated to c.customer_id from the outermost query, and the middle EXISTS subquery is also correlated to c.customer_id. Each is logically re-evaluated per candidate customer, though the planner may rewrite this into a join-like plan internally.

IN vs EXISTS vs NOT EXISTS

IN and EXISTS often look interchangeable, but they behave differently, especially at scale:

-- Using IN
SELECT name FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o WHERE o.amount > 500);

-- Using EXISTS (correlated)
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id AND o.amount > 500
);

Both queries return the same result, but their execution strategies differ. IN conceptually needs the full list of matching values from the subquery before it can test membership; historically, and still in some plan shapes today, this meant materializing the entire subquery result (or de-duplicating it) before probing it for each outer row. EXISTS, on the other hand, is a existence check: PostgreSQL can stop scanning the inner query for a given outer row as soon as it finds one matching row (a “semi-join” with short-circuit behavior), rather than computing the complete inner result set.

In modern PostgreSQL (9.x+), the planner is often smart enough to rewrite an IN subquery into a semi-join plan that performs just as well as EXISTS, especially when the subquery involves a simple, indexed equality. But this rewrite is not guaranteed in every case — it depends on subquery shape, correlation, and whether the planner recognizes it as safely transformable. EXISTS gives you a stronger guarantee of short-circuiting behavior and tends to be more robust when:

-- Customers who have never placed an order
-- NOT EXISTS: safe, always correct
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- NOT IN: dangerous if orders.customer_id can ever be NULL
SELECT c.name FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o);  -- returns 0 rows if any o.customer_id IS NULL

The practical rule of thumb: prefer EXISTS/NOT EXISTS for correlated existence checks and whenever NULL-safety matters, and don’t assume IN is automatically slower — always check with EXPLAIN ANALYZE on your actual data volumes (see 10 — Query Planning and Performance Tuning).

Key Concepts

Common Table Expressions (CTEs)

A CTE, introduced with the WITH clause, lets you give a name to a subquery and reference it later in the statement as if it were a table. The primary benefit is readability: instead of nesting nine levels of subqueries, you break a complex query into named, sequential logical steps.

WITH high_value_customers AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) > 5000
),
recent_orders AS (
    SELECT customer_id, order_id, order_date, amount
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
),
combined AS (
    SELECT hvc.customer_id, hvc.total_spent, ro.order_id, ro.order_date, ro.amount
    FROM high_value_customers hvc
    JOIN recent_orders ro ON ro.customer_id = hvc.customer_id
)
SELECT c.name, combined.total_spent, combined.order_id, combined.order_date, combined.amount
FROM combined
JOIN customers c ON c.customer_id = combined.customer_id
ORDER BY combined.total_spent DESC, combined.order_date DESC;

Each WITH block reads like a step in a pipeline: first find high-value customers, then find recent orders, then join them together, then decorate with customer names. This is dramatically easier to review and modify than an equivalent query nested with nine levels of parentheses, and each named CTE can be tested independently by running it as a standalone SELECT while developing the query.

The PostgreSQL CTE optimization fence (materialization) — a PostgreSQL-specific gotcha

This is one of the most important PostgreSQL-specific details for anyone coming from another RDBMS, and it changed significantly between versions:

-- PostgreSQL 12+: MATERIALIZED forces a hard optimization fence, exactly like pre-12 default behavior
WITH big_cte AS MATERIALIZED (
    SELECT * FROM orders
)
SELECT * FROM big_cte WHERE customer_id = 42;

-- PostgreSQL 12+: NOT MATERIALIZED forces inlining even if the planner would rather materialize
WITH filtered AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM filtered WHERE customer_id = 42;

MATERIALIZED is still genuinely useful — for example, when a CTE is expensive to compute and referenced multiple times in the outer query, forcing materialization avoids recomputing it, or when the CTE has volatile functions (e.g., random(), sequence-consuming functions) whose results must be stable across all references. Recursive CTEs (below) are always materialized regardless of version, since the recursive evaluation model requires it.

The takeaway for anyone auditing or writing queries against production PostgreSQL: if a query is running on PostgreSQL 11 or earlier and uses CTEs heavily with filters, check whether rewriting as subqueries (or upgrading) improves the plan, and always confirm actual behavior with EXPLAIN (ANALYZE, BUFFERS) rather than assuming based on version alone.

Recursive CTEs

WITH RECURSIVE is PostgreSQL’s mechanism for traversing hierarchical or graph-like data: org charts, category trees, bill-of-materials explosions, dependency graphs, and pathfinding. A recursive CTE has two parts, combined with UNION or UNION ALL:

  1. The base case (anchor member): a non-recursive SELECT that produces the starting row(s).
  2. The recursive case (recursive member): a SELECT that references the CTE’s own name, joining the previous iteration’s output to produce the next level. PostgreSQL runs this repeatedly, feeding each iteration’s result back in as input, until the recursive member returns zero rows.

Here is a full worked example traversing an employee-manager hierarchy to find everyone who reports, directly or indirectly, to a given manager, along with their depth in the org chart:

WITH RECURSIVE org_chart AS (
    -- Base case: the manager we're starting from (depth 0)
    SELECT employee_id, name, manager_id, department, 0 AS depth,
           name::text AS path
    FROM employees
    WHERE employee_id = 1  -- e.g. the CEO

    UNION ALL

    -- Recursive case: find direct reports of everyone already in org_chart
    SELECT e.employee_id, e.name, e.manager_id, e.department, oc.depth + 1,
           oc.path || ' -> ' || e.name
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT employee_id, name, department, depth, path
FROM org_chart
ORDER BY depth, name;

Walking through the execution: PostgreSQL first runs the anchor member and puts its rows into a working table (conceptually, employee id 1). Then it repeatedly runs the recursive member, but each iteration only sees the rows added in the previous iteration as org_chart (not the full accumulated result) — so the first recursive pass finds direct reports of the CEO, the second pass finds direct reports of those people, and so on. Each iteration’s new rows are added to the final result and fed back in, and the process stops automatically once an iteration produces no new rows (e.g., once you reach employees with no direct reports).

A few practical notes on recursive CTEs:

WITH RECURSIVE org_chart AS (
    SELECT employee_id, manager_id, ARRAY[employee_id] AS visited, 0 AS depth
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    SELECT e.employee_id, e.manager_id, oc.visited || e.employee_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
    WHERE NOT e.employee_id = ANY(oc.visited)  -- cycle guard
)
SELECT * FROM org_chart;

LATERAL joins

An ordinary JOIN cannot have its right-hand side reference columns from its left-hand side — each side of a plain join is evaluated independently, and only combined afterward via the join condition. LATERAL (implicit for functions, explicit via the LATERAL keyword for subqueries) changes this: it lets the subquery or function on the right-hand side reference columns from tables appearing earlier in the same FROM clause, effectively running the right-hand side once per row of the left-hand side, like a correlated subquery embedded directly in the FROM clause instead of the WHERE clause.

The canonical example this unlocks is “top N per group” — for instance, the 3 most recent orders for each customer:

SELECT c.name, top_orders.order_id, top_orders.order_date, top_orders.amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.order_id, o.order_date, o.amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
    LIMIT 3
) AS top_orders
ORDER BY c.name, top_orders.order_date DESC;

Notice the subquery references c.customer_id — that column reference is exactly what makes this LATERAL; without the keyword, PostgreSQL would reject the query with an error because a plain subquery in FROM cannot see c at all. The LIMIT 3 inside the lateral subquery is per-customer, which is precisely the point.

Why is this pattern hard to express without LATERAL? A window function like ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) can also solve “top 3 per group,” but it requires computing the row number across the entire orders table and then filtering in an outer query (WHERE rn <= 3), because window functions cannot appear directly in a WHERE clause. That is often fine, but it means scanning and ranking every row in orders, even customers you might not care about, and it becomes awkward once the “top N” logic needs additional per-row computation (e.g., calling a set-returning function, or needing a LIMIT that itself depends on a per-group value, like “top N where N is stored in a customers.vip_tier column”). A plain (non-lateral) join simply cannot do this at all — there is no way to say “join each customer to a filtered, sorted, limited slice of their own orders” using JOIN ... ON alone, because the ON condition only relates matching rows, it cannot control ordering or limit within the join. LATERAL is the direct, composable tool for “for each row on the left, compute this correlated, possibly limited, possibly set-returning result on the right,” including calling table-returning functions per row, which window functions cannot do at all.

Aggregate functions vs. window functions

Both aggregate and window functions compute things like sums, counts, and averages over sets of rows, but they differ fundamentally in what they do to the result set’s shape.

An aggregate function used with GROUP BY collapses each group of input rows into a single output row:

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

If orders has a thousand rows across fifty customers, this query returns exactly fifty rows — one per customer — and the individual order rows are gone from the output.

A window function, using the OVER (...) clause, computes an aggregate-like value across a set of related rows (the “window”) but returns one output row per input row, keeping all original columns intact:

SELECT order_id, customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
       COUNT(*) OVER (PARTITION BY customer_id) AS customer_order_count
FROM orders;

If orders has a thousand rows, this query still returns a thousand rows — each order row now also carries its customer’s total spend and order count alongside it, computed without collapsing anything.

Anatomy of the OVER clause

The window clause has three optional parts:

Common window functions

ROW_NUMBER() assigns a strictly increasing, unique integer per partition, with no ties:

SELECT employee_id, name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

RANK() and DENSE_RANK() handle ties differently. RANK() gives tied rows the same rank but leaves a gap afterward (1, 2, 2, 4); DENSE_RANK() gives tied rows the same rank with no gap (1, 2, 2, 3):

SELECT employee_id, name, department, salary,
       RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_with_gaps,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_no_gaps
FROM employees;

LAG() and LEAD() look at a row before or after the current row within the ordered window, without needing a self-join:

SELECT customer_id, order_id, order_date, amount,
       LAG(amount)  OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_amount,
       LEAD(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_amount,
       amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS change_from_prev
FROM orders;

Worked example: running total and rank within groups

This example combines a running total (cumulative sum ordered by date, per customer) with a rank of employees by salary within their department, showing both patterns side by side:

-- Running total of order amount per customer, ordered by date
SELECT customer_id, order_id, order_date, amount,
       SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY customer_id, order_date;

-- Rank of each employee's salary within their department
SELECT employee_id, name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY department, salary_rank;

In the first query, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly defines the frame as “every row from the start of this customer’s partition up to and including the current row” — this is in fact the default frame when ORDER BY is present without an explicit frame clause, but writing it explicitly makes the intent unambiguous. In the second query, RANK() answers “where does this employee’s salary rank compared to peers in the same department,” which is a question GROUP BY alone cannot answer, since GROUP BY would collapse away the individual employee rows entirely.

Window functions can also be combined with a CTE to filter on the computed value, since window functions cannot appear directly in WHERE:

WITH ranked_employees AS (
    SELECT employee_id, name, department, salary,
           RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT * FROM ranked_employees WHERE salary_rank <= 2;  -- top 2 earners per department

Comparison tables

CTE vs. subquery vs. LATERAL join

AspectCTE (WITH)SubqueryLATERAL join
ReadabilityHigh — named, sequential stepsLower — nesting obscures logicMedium — explicit correlation is visible in FROM
Reusable within the queryYes, reference the CTE name multiple timesNo, must repeat the subquery text (or the planner may re-evaluate it)No, evaluated per left-hand row, not reused across rows
Can reference outer-query columns (correlation)No (non-recursive CTEs are evaluated independently; recursive CTEs reference themselves, not an outer query)Yes, if written as a correlated subqueryYes — this is its defining feature
Optimization behaviorPG 12+: inlined by default like a subquery unless MATERIALIZED; pre-12: always a fenceNormally inlined/merged into the plan by the plannerEvaluated per outer row; not a fence, but inherently row-by-row in cost
Typical use caseBreaking a complex query into named logical steps; recursive traversalScalar lookups, IN/EXISTS membership tests”Top N per group,” calling a set-returning function per row, per-row LIMIT

Aggregate function vs. window function

AspectAggregate function (with GROUP BY)Window function (OVER (...))
Rows in vs. rows outMany rows in, one row out per groupMany rows in, same number of rows out
Collapses detail rowsYesNo — original row-level columns are preserved
Can be used in WHERENo (must use HAVING for post-aggregation filters)No (must wrap in a CTE/subquery and filter in the outer query)
Typical use caseTotals/summaries per group (“total spend per customer”)Running totals, rankings, comparisons to neighboring rows, per-row context alongside detail data

Best Practices

References