Subquery และ CTE

subquery คือ SELECT ที่เขียนอยู่ในอีก statement หนึ่ง query ด้านในทำงานก่อนแล้วส่งผลให้ query ด้านนอก ทำให้ตอบคำถามเป็นสองขั้นได้: “หาค่าเฉลี่ย” แล้ว “แสดง row ที่สูงกว่านั้น” common table expression หรือ CTE ใช้แนวคิดเดียวกันแต่ตั้งชื่อให้ผลระหว่างทางด้วย WITH query ยาว ๆ จึงอ่านเหมือนเรื่องสั้นแทนที่จะเป็นวงเล็บซ้อนกันยุ่งเหยิง

ทุกอย่างด้านล่างรันบน PostgreSQL 18.6 (postgres:18.6, aarch64, Docker Desktop บน Mac, กันยายน 2026) คัดลอกจาก psql fence sql รันจากบนลงล่างในฐานข้อมูลเปล่าหนึ่งตัว ตัวเลขเวลามาจาก Mac เครื่องนี้

ชุดข้อมูล

ชุดข้อมูลมาตรฐานของ module นี้ สังเกตสองข้อเท็จจริงที่บทนี้พึ่งพา: Unlinked Draft มี author_id = NULL และ Sela Vance ไม่เคยเขียนหนังสือ

-- sql/setup.sql
CREATE TABLE authors (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  born int
);
CREATE TABLE books (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL,
  author_id   bigint REFERENCES authors (id),
  published   int,
  copies_sold int NOT NULL DEFAULT 0
);
CREATE TABLE sales (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id bigint REFERENCES books (id),
  region  text NOT NULL,
  copies  int NOT NULL
);
INSERT INTO authors (name, born) VALUES ('Mara Linde', 1968), ('Owen Pryce', 1975), ('Sela Vance', 1990);
INSERT INTO books (title, author_id, published, copies_sold) VALUES
  ('Quiet Harbors', 1, 2003, 450), ('The Glass Orchard', 1, 2018, 1250), ('Tidal Notes', 2, 2011, 600), ('Unlinked Draft', NULL, 2020, 0);
INSERT INTO sales (book_id, region, copies) VALUES
  (2, 'East', 900), (3, 'West', 400), (1, 'West', 400), (3, 'East', 200), (2, 'West', 150), (1, 'East', 50);

Scalar subquery

scalar subquery คืนค่าเดียวเป๊ะ จึงวางได้ทุกที่ที่ต้องการค่าเดียว รวมถึงในการเปรียบเทียบ ตรงนี้ list หนังสือที่ตีพิมพ์หลังปีเฉลี่ย (สี่ปีเฉลี่ยได้ 2013)

psql

-- sql/scalar-subquery.sql
SELECT title, published
FROM books
WHERE published > (SELECT avg(published) FROM books)
ORDER BY title;

Node.js

const res = await pool.query(`
  SELECT title, published
  FROM books
  WHERE published > (SELECT avg(published) FROM books)
  ORDER BY title
`);

Python

cur.execute("""
    SELECT title, published
    FROM books
    WHERE published > (SELECT avg(published) FROM books)
    ORDER BY title
""")
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
  SELECT title, published
  FROM books
  WHERE published > (SELECT avg(published) FROM books)
  ORDER BY title`)
defer rows.Close()

Rust

let rows = sqlx::query(
    "SELECT title, published
     FROM books
     WHERE published > (SELECT avg(published) FROM books)
     ORDER BY title",
)
.fetch_all(&pool)
.await?;

       title       | published
-------------------+-----------
 The Glass Orchard |      2018
 Unlinked Draft    |      2020
(2 rows)

ถ้า subquery คืนมากกว่าหนึ่ง row ในที่ที่ต้องการค่าเดียว PostgreSQL ไม่เดาให้:

-- @expect-error 21000
SELECT title, (SELECT name FROM authors) AS author FROM books;

ERROR:  more than one row returned by a subquery used as an expression

IN และ EXISTS subquery

IN เทียบค่ากับ list ที่ subquery สร้าง; EXISTS ถามว่า subquery สร้าง row ใด ๆ หรือไม่ และมักเป็น correlated กับ row ด้านนอก ทั้งสองตอบ “ผู้เขียนที่เขียนหนังสืออย่างน้อยหนึ่งเล่ม”:

psql

-- sql/exists.sql
SELECT name
FROM authors a
WHERE EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id)
ORDER BY name;

Node.js

const res = await pool.query(`
  SELECT name
  FROM authors a
  WHERE EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id)
  ORDER BY name
`);

Python

cur.execute("""
    SELECT name
    FROM authors a
    WHERE EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id)
    ORDER BY name
""")
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
  SELECT name
  FROM authors a
  WHERE EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id)
  ORDER BY name`)
defer rows.Close()

Rust

let rows = sqlx::query(
    "SELECT name
     FROM authors a
     WHERE EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id)
     ORDER BY name",
)
.fetch_all(&pool)
.await?;

    name
------------
 Mara Linde
 Owen Pryce
(2 rows)

-- sql/in.sql
SELECT name FROM authors WHERE id IN (SELECT author_id FROM books) ORDER BY name;

    name
------------
 Mara Linde
 Owen Pryce
(2 rows)

อันไหนเร็วกว่า? ใน PostgreSQL ไม่ต่างกัน หัวข้อนี้สร้าง parents 1 000 และ children 200 000 (เฉพาะ parent 1 ถึง 500 ที่มี children) พร้อม index บน children.parent_id:

-- sql/subquery-plans-setup.sql
CREATE TABLE parents (id int PRIMARY KEY);
CREATE TABLE children (
  id        int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  parent_id int NOT NULL REFERENCES parents (id),
  k         int NOT NULL
);
INSERT INTO parents SELECT g FROM generate_series(1, 1000) AS g;
-- only parents 1..500 have children
INSERT INTO children (parent_id, k) SELECT 1 + g % 500, g % 10 FROM generate_series(1, 200000) AS g;
CREATE INDEX children_parent_id_idx ON children (parent_id);
ANALYZE parents;
ANALYZE children;

CREATE TABLE
CREATE TABLE
INSERT 0 1000
INSERT 0 200000
CREATE INDEX
ANALYZE
ANALYZE

สองแบบด้านล่างวางเป็น plan เดียวกันเป๊ะ จนถึงตัวเลข cost planner เขียนทั้งสองใหม่เป็น semi join (docs เรื่อง subquery expression) คำแนะนำ “EXISTS หยุดที่ match แรกได้” คือสิ่งที่ Semi Join ทำให้อยู่แล้วไม่ว่าจะเขียนแบบไหน:

-- sql/plan-in-vs-exists.sql
EXPLAIN SELECT count(*) FROM parents WHERE id IN (SELECT parent_id FROM children);
EXPLAIN SELECT count(*) FROM parents p WHERE EXISTS (SELECT 1 FROM children c WHERE c.parent_id = p.id);

                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=366.17..366.18 rows=1 width=8)
   ->  Nested Loop Semi Join  (cost=0.29..364.92 rows=500 width=0)
         ->  Seq Scan on parents  (cost=0.00..15.00 rows=1000 width=4)
         ->  Index Only Scan using children_parent_id_idx on children  (cost=0.29..12.30 rows=400 width=4)
               Index Cond: (parent_id = parents.id)
(5 rows)

                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=366.17..366.18 rows=1 width=8)
   ->  Nested Loop Semi Join  (cost=0.29..364.92 rows=500 width=0)
         ->  Seq Scan on parents p  (cost=0.00..15.00 rows=1000 width=4)
         ->  Index Only Scan using children_parent_id_idx on children c  (cost=0.29..12.30 rows=400 width=4)
               Index Cond: (parent_id = p.id)
(5 rows)

NOT IN และกับดัก NULL

NOT IN ต่างออกไป และนี่คือความผิดพลาดกับ subquery ที่มีผลจริง x NOT IN (1, 2, NULL) ไม่เคยเป็นจริง: สำหรับค่าที่ไม่อยู่ใน list มันประเมินเป็น NULL (ไม่ทราบ) ไม่ใช่ true books.author_id ของเรามี NULL หนึ่งตัว “ผู้เขียนที่ไม่มีหนังสือ” จึงคืน ว่างเปล่า ทั้งที่คำตอบคือ Sela Vance:

-- sql/not-in-vs-not-exists.sql
-- books.author_id contains a NULL (Unlinked Draft), so NOT IN can never be true
SELECT name FROM authors WHERE id NOT IN (SELECT author_id FROM books) ORDER BY name;

SELECT name FROM authors a
WHERE NOT EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id)
ORDER BY name;

 name
------
(0 rows)

    name
------------
 Sela Vance
(1 row)

การกรอง NULL ออกจาก subquery แก้ได้ แต่ NOT EXISTS ไม่เคยมีปัญหานี้:

-- sql/not-in-filtered.sql
-- NOT IN works again once the NULLs are removed from the subquery
SELECT name FROM authors
WHERE id NOT IN (SELECT author_id FROM books WHERE author_id IS NOT NULL)
ORDER BY name;

    name
------------
 Sela Vance
(1 row)

นี่เป็นปัญหาของ plan ด้วย NOT EXISTS กลายเป็น anti join ส่วน NOT IN ทำไม่ได้ (เพราะกฎของ NULL) และเป็นตัวกรองบน hashed subplan:

-- sql/plan-not-in-vs-not-exists.sql
EXPLAIN (COSTS OFF) SELECT count(*) FROM parents WHERE id NOT IN (SELECT parent_id FROM children);
EXPLAIN (COSTS OFF) SELECT count(*) FROM parents p WHERE NOT EXISTS (SELECT 1 FROM children c WHERE c.parent_id = p.id);

                         QUERY PLAN
------------------------------------------------------------
 Aggregate
   ->  Seq Scan on parents
         Filter: (NOT (ANY (id = (hashed SubPlan 1).col1)))
         SubPlan 1
           ->  Seq Scan on children
(5 rows)

                               QUERY PLAN
------------------------------------------------------------------------
 Aggregate
   ->  Nested Loop Anti Join
         ->  Seq Scan on parents p
         ->  Index Only Scan using children_parent_id_idx on children c
               Index Cond: (parent_id = p.id)
(5 rows)

Correlated subquery

correlated subquery อ้างถึง row ด้านนอก จึงในเชิงแนวคิดรันหนึ่งครั้งต่อ row ด้านนอก ในตำแหน่ง scalar นั่นคือสิ่งที่ plan ทำจริง (SubPlan 1 ... loops=1000) และตรงนี้ช้ากว่า join กับ GROUP BY ที่ให้คำตอบเดียวกันราวสามเท่า (165 ms เทียบกับ 51 ms บน Mac เครื่องนี้ parents 1 000 และ children 200 000 การรันครั้งแรกยังจ่ายค่าอ่าน buffer แบบเย็น 165 ครั้ง):

-- sql/correlated-subquery.sql
-- a correlated scalar subquery cannot be flattened: one probe per parent
EXPLAIN (ANALYZE, COSTS OFF)
SELECT p.id, (SELECT count(*) FROM children c WHERE c.parent_id = p.id) AS n
FROM parents p;

-- the same answer as a join + GROUP BY
EXPLAIN (ANALYZE, COSTS OFF)
SELECT p.id, count(c.id) AS n
FROM parents p LEFT JOIN children c ON c.parent_id = p.id
GROUP BY p.id;

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on parents p (actual time=63.716..151.630 rows=1000.00 loops=1)
   Buffers: shared hit=202006 read=165
   SubPlan 1
     ->  Aggregate (actual time=0.088..0.089 rows=1.00 loops=1000)
           Buffers: shared hit=202001 read=165
           ->  Bitmap Heap Scan on children c (actual time=0.018..0.081 rows=200.00 loops=1000)
                 Recheck Cond: (parent_id = p.id)
                 Heap Blocks: exact=200000
                 Buffers: shared hit=202001 read=165
                 ->  Bitmap Index Scan on children_parent_id_idx (actual time=0.007..0.007 rows=200.00 loops=1000)
                       Index Cond: (parent_id = p.id)
                       Index Searches: 1000
                       Buffers: shared hit=2001 read=165
 Planning:
   Buffers: shared hit=95
 Planning Time: 0.225 ms
 Execution Time: 164.893 ms
(17 rows)

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 HashAggregate (actual time=50.868..50.950 rows=1000.00 loops=1)
   Group Key: p.id
   Batches: 1  Memory Usage: 121kB
   Buffers: shared hit=1087
   ->  Hash Right Join (actual time=0.115..33.041 rows=200500.00 loops=1)
         Hash Cond: (c.parent_id = p.id)
         Buffers: shared hit=1087
         ->  Seq Scan on children c (actual time=0.003..9.376 rows=200000.00 loops=1)
               Buffers: shared hit=1082
         ->  Hash (actual time=0.109..0.109 rows=1000.00 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 44kB
               Buffers: shared hit=5
               ->  Seq Scan on parents p (actual time=0.004..0.048 rows=1000.00 loops=1)
                     Buffers: shared hit=5
 Planning:
   Buffers: shared hit=40
 Planning Time: 0.208 ms
 Execution Time: 51.021 ms
(18 rows)

EXISTS แบบ correlated ไม่เป็นไร เพราะถูกแปลงเป็น semi join ตามที่แสดงข้างบน ที่ยังเป็นการ probe ต่อ row คือ correlated subquery แบบ scalar (รายการใน SELECT)

CTE: ตั้งชื่อขั้นตอนด้วย WITH

CTE ตั้งชื่อให้ subquery และวางไว้ก่อน query หลักจึงอ่านจากบนลงล่าง ตรงนี้รวมยอดขายต่อเล่ม แล้ว join ชื่อหนังสือ:

psql

-- sql/cte.sql
WITH totals AS (
  SELECT book_id, sum(copies) AS copies
  FROM sales
  GROUP BY book_id
)
SELECT b.title, t.copies
FROM totals AS t
JOIN books AS b ON b.id = t.book_id
ORDER BY t.copies DESC;

Node.js

const res = await pool.query(`
  WITH totals AS (
    SELECT book_id, sum(copies) AS copies
    FROM sales
    GROUP BY book_id
  )
  SELECT b.title, t.copies
  FROM totals AS t
  JOIN books AS b ON b.id = t.book_id
  ORDER BY t.copies DESC
`);

Python

cur.execute("""
    WITH totals AS (
      SELECT book_id, sum(copies) AS copies
      FROM sales
      GROUP BY book_id
    )
    SELECT b.title, t.copies
    FROM totals AS t
    JOIN books AS b ON b.id = t.book_id
    ORDER BY t.copies DESC
""")
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
  WITH totals AS (
    SELECT book_id, sum(copies) AS copies
    FROM sales
    GROUP BY book_id
  )
  SELECT b.title, t.copies
  FROM totals AS t
  JOIN books AS b ON b.id = t.book_id
  ORDER BY t.copies DESC`)
defer rows.Close()

Rust

let rows = sqlx::query(
    "WITH totals AS (
       SELECT book_id, sum(copies) AS copies
       FROM sales
       GROUP BY book_id
     )
     SELECT b.title, t.copies
     FROM totals AS t
     JOIN books AS b ON b.id = t.book_id
     ORDER BY t.copies DESC",
)
.fetch_all(&pool)
.await?;

       title       | copies
-------------------+--------
 The Glass Orchard |   1050
 Tidal Notes       |    600
 Quiet Harbors     |    450
(3 rows)

เชื่อม CTE หลายตัวด้วย comma ได้ แต่ละตัวมองเห็นตัวก่อนหน้า จำนวนเล่มที่ขายต่อผู้เขียน:

-- sql/cte-chain.sql
WITH per_book AS (
  SELECT book_id, sum(copies) AS copies FROM sales GROUP BY book_id
), per_author AS (
  SELECT b.author_id, sum(p.copies) AS copies
  FROM per_book p JOIN books b ON b.id = p.book_id
  GROUP BY b.author_id
)
SELECT a.name, pa.copies
FROM per_author pa JOIN authors a ON a.id = pa.author_id
ORDER BY pa.copies DESC;

    name    | copies
------------+--------
 Mara Linde |   1500
 Owen Pryce |    600
(2 rows)

ผลลัพธ์จาก driver สำหรับสาม query ข้างบน แต่ละอันรันหนึ่งครั้ง:

$ (each snippet run once against the dataset above; multi-line driver output joined onto one line)
[scalar] Node.js (pg 8.23.0):    [ { title: 'The Glass Orchard', published: 2018 }, { title: 'Unlinked Draft', published: 2020 } ]
[scalar] Python (psycopg 3.3.6): [('The Glass Orchard', 2018), ('Unlinked Draft', 2020)]
[scalar] Go (pgx v5.11.0):       [The Glass Orchard 2018] [Unlinked Draft 2020]
[scalar] Rust (sqlx 0.9.0):      The Glass Orchard 2018 / Unlinked Draft 2020
[exists] Node.js:                [ { name: 'Mara Linde' }, { name: 'Owen Pryce' } ]
[exists] Python:                 [('Mara Linde',), ('Owen Pryce',)]
[exists] Go / Rust:              Mara Linde / Owen Pryce
[cte]    Node.js:                [ { title: 'The Glass Orchard', copies: '1050' }, { title: 'Tidal Notes', copies: '600' }, { title: 'Quiet Harbors', copies: '450' } ]
[cte]    Python:                 [('The Glass Orchard', 1050), ('Tidal Notes', 600), ('Quiet Harbors', 450)]
[cte]    Go / Rust:              The Glass Orchard 1050 / Tidal Notes 600 / Quiet Harbors 450

CTE เป็นรั้วกั้นไหม? แล้วแต่ และคุณเลือกได้

ตั้งแต่ PostgreSQL 12 CTE จะถูก inline เข้าไปใน query ด้านนอกเมื่อไม่ recursive ไม่มี side effect และถูกอ้างถึงเพียงครั้งเดียว มิฉะนั้นมันถูกคำนวณครั้งเดียวและใช้ผลซ้ำ (materialize) AS MATERIALIZED และ AS NOT MATERIALIZED override ค่าเริ่มต้น (docs) สี่ plan ด้านล่างแสดงครบสี่รูปแบบ บน children 200 000 row:

-- sql/cte-inline.sql
-- referenced once: inlined into the outer query
EXPLAIN (COSTS OFF)
WITH c AS (SELECT * FROM children WHERE k = 5)
SELECT * FROM c WHERE id < 100;

-- referenced twice: computed once and shared (materialized)
EXPLAIN (COSTS OFF)
WITH c AS (SELECT * FROM children WHERE k = 5)
SELECT (SELECT count(*) FROM c) AS n, (SELECT max(id) FROM c) AS newest;

-- forced with AS MATERIALIZED even though used once
EXPLAIN (COSTS OFF)
WITH c AS MATERIALIZED (SELECT * FROM children WHERE k = 5)
SELECT * FROM c WHERE id < 100;

-- forced inlining with NOT MATERIALIZED even though used twice
EXPLAIN (COSTS OFF)
WITH c AS NOT MATERIALIZED (SELECT * FROM children WHERE k = 5)
SELECT (SELECT count(*) FROM c) AS n, (SELECT max(id) FROM c) AS newest;

                 QUERY PLAN
--------------------------------------------
 Index Scan using children_pkey on children
   Index Cond: (id < 100)
   Filter: (k = 5)
(3 rows)

           QUERY PLAN
---------------------------------
 Result
   CTE c
     ->  Seq Scan on children
           Filter: (k = 5)
   InitPlan 2
     ->  Aggregate
           ->  CTE Scan on c
   InitPlan 3
     ->  Aggregate
           ->  CTE Scan on c c_1
(10 rows)

          QUERY PLAN
------------------------------
 CTE Scan on c
   Filter: (id < 100)
   CTE c
     ->  Seq Scan on children
           Filter: (k = 5)
(5 rows)

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Result
   InitPlan 1
     ->  Finalize Aggregate
           ->  Gather
                 Workers Planned: 1
                 ->  Partial Aggregate
                       ->  Parallel Seq Scan on children
                             Filter: (k = 5)
   InitPlan 3
     ->  Result
           InitPlan 2
             ->  Limit
                   ->  Index Scan Backward using children_pkey on children children_1
                         Filter: (k = 5)
(14 rows)

1. อ้างถึงครั้งเดียว: ไม่มี node CTE เลย เงื่อนไข id < 100 ด้านนอกถูกผลักเข้าไปใน scan ของ CTE เอง (Index Cond: (id < 100), Filter: (k = 5))
2. อ้างถึงสองครั้ง: CTE c ถูก scan ครั้งเดียวและอ่านโดยสอง node CTE Scan
3. AS MATERIALIZED กับการอ้างถึงเดียว: CTE ถูกคำนวณเต็มก่อน และ id < 100 เป็นเพียง Filter บน CTE Scan คือรั้วกั้นการ optimize
4. NOT MATERIALIZED กับสองการอ้างถึง: inline สองครั้ง แต่ละการอ้างถึงได้ plan ของตัวเอง (max(id) กลายเป็น backward index scan ด้วยซ้ำ)

ไม่มีแบบไหนดีกว่าโดยทั่วไป materialize เมื่อ CTE แพงและถูกอ่านหลายครั้ง inline เมื่อ query ด้านนอกกรองมันได้

WITH RECURSIVE

CTE แบบ recursive มี query เริ่มต้น, UNION ALL และส่วน recursive ที่อ้างถึง CTE ตัวเอง วนซ้ำจนส่วน recursive ไม่คืน row ใหม่ เป็นวิธีมาตรฐานในการเดินต้นไม้ เช่นผังองค์กร:

-- sql/recursive-setup.sql
CREATE TABLE staff (
  id      int PRIMARY KEY,
  name    text NOT NULL,
  boss_id int REFERENCES staff (id)
);
INSERT INTO staff VALUES
  (1, 'Ada', NULL), (2, 'Ben', 1), (3, 'Cy', 1), (4, 'Di', 2), (5, 'Eli', 2), (6, 'Flo', 3), (7, 'Gus', 4);

-- sql/recursive.sql
WITH RECURSIVE tree AS (
  SELECT id, name, 0 AS depth, name AS path
  FROM staff
  WHERE boss_id IS NULL
  UNION ALL
  SELECT s.id, s.name, t.depth + 1, t.path || ' > ' || s.name
  FROM staff s
  JOIN tree t ON s.boss_id = t.id
)
SELECT repeat('  ', depth) || name AS org_chart, path
FROM tree
ORDER BY path;

 org_chart |         path
-----------+----------------------
 Ada       | Ada
   Ben     | Ada > Ben
     Di    | Ada > Ben > Di
       Gus | Ada > Ben > Di > Gus
     Eli   | Ada > Ben > Eli
   Cy      | Ada > Cy
     Flo   | Ada > Cy > Flo
(7 rows)

ข้อมูลเสียทำให้ recursion ไม่จบได้: ถ้ากราฟมี cycle ส่วน recursive เจอ row ใหม่ไปเรื่อย ๆ clause CYCLE (PostgreSQL 14+) ทำเครื่องหมาย row ที่กลับมาที่ id ที่เคยเห็นแล้วและหยุดตรงนั้น เราทำข้อมูลเสียใน transaction ให้ Ada รายงานต่อเหลนของตัวเองคือ Gus แล้ว rollback:

-- sql/recursive-cycle.sql
BEGIN;
-- corrupt the data on purpose: Ada now reports to Gus, who reports (via Di, Ben) to Ada
UPDATE staff SET boss_id = 7 WHERE id = 1;
WITH RECURSIVE tree AS (
  SELECT id, name, boss_id FROM staff WHERE id = 1
  UNION ALL
  SELECT s.id, s.name, s.boss_id FROM staff s JOIN tree t ON s.boss_id = t.id
) CYCLE id SET is_cycle USING seen
SELECT id, name, is_cycle FROM tree;
ROLLBACK;

BEGIN
UPDATE 1
 id | name | is_cycle
----+------+----------
  1 | Ada  | f
  2 | Ben  | f
  3 | Cy   | f
  4 | Di   | f
  5 | Eli  | f
  6 | Flo  | f
  7 | Gus  | f
  1 | Ada  | t
(8 rows)

ROLLBACK

row สุดท้าย (is_cycle = t) คือ Ada ที่เห็นเป็นครั้งที่สอง recursion หยุดแทนที่จะวนไม่รู้จบ

CTE ที่แก้ข้อมูล

DELETE, UPDATE และ INSERT อยู่ใน WITH ได้ถ้ามี RETURNING และผลของมันป้อนให้ statement หลักได้ ตัวอย่างนี้ย้ายยอดขาย West ไป table archive ใน statement เดียว (อยู่ใน transaction เพื่อให้ดูแล้ว rollback ได้):

-- sql/data-modifying-cte.sql
CREATE TABLE sales_archive (LIKE sales);
BEGIN;
WITH moved AS (
  DELETE FROM sales WHERE region = 'West' RETURNING *
)
INSERT INTO sales_archive SELECT * FROM moved;
SELECT (SELECT count(*) FROM sales) AS live, (SELECT count(*) FROM sales_archive) AS archived;
ROLLBACK;
SELECT (SELECT count(*) FROM sales) AS live, (SELECT count(*) FROM sales_archive) AS archived;

CREATE TABLE
BEGIN
INSERT 0 3
 live | archived
------+----------
    3 |        3
(1 row)

ROLLBACK
 live | archived
------+----------
    6 |        0
(1 row)

ใน transaction มี 3 row live และ 3 archived หลัง rollback archive ว่างอีกครั้ง

Reading AI-generated code

ผู้ช่วย AI ถูกขอ “ผู้เขียนที่ไม่เคยเขียนหนังสือ” และเขียน query ที่ดูเป็นธรรมชาติที่สุด:

🐛 หา bug ในโค้ดนี้

-- AI comment: "authors who have not written any book"
SELECT name
FROM authors
WHERE id NOT IN (SELECT author_id FROM books);

เฉลย

มันคืนว่างเปล่า และไม่มี error books.author_id มี NULL (Unlinked Draft) id NOT IN (..., NULL) จึงไม่เคยเป็นจริง และ Sela Vance ตกหล่น:

-- sql/stb-wrong.sql
SELECT name
FROM authors
WHERE id NOT IN (SELECT author_id FROM books);

 name
------
(0 rows)

NOT EXISTS ระบุเจตนาโดยไม่มีภัยจาก NULL (และวาง plan เป็น anti join):

-- sql/stb-fixed.sql
SELECT name
FROM authors a
WHERE NOT EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id);

    name
------------
 Sela Vance
(1 row)

Mental Model

subquery คือ expression ที่ค่าเป็น table, row หรือค่าเดียว ถามว่ามันต้องเป็นอะไร แล้ว syntax ตามมาเอง: ค่าเดียว (เปรียบเทียบแบบ scalar), list สำหรับทดสอบการเป็นสมาชิก (IN) หรือแค่ “ว่างไหม?” (EXISTS) CTE คือ subquery ที่มีชื่อวางไว้ด้านบน มันเป็นเครื่องมือตั้งชื่อก่อน ส่วนจะเป็นขอบเขตการคำนวณด้วยหรือไม่ planner ตัดสินและคุณ override ได้ recursion เพิ่มความคิดเดียว: CTE ที่ป้อน row ใหม่ของตัวเองกลับเข้าไปจนไม่เหลือ

เคล็ดลับและข้อควรระวัง

• scalar subquery ต้องคืนหนึ่ง row หนึ่ง column ไม่เช่นนั้น PostgreSQL ส่ง 21000
• IN และ EXISTS (และ NOT EXISTS) วาง plan เหมือนกัน; เลือก NOT EXISTS แทน NOT IN เมื่อ column ของ subquery เป็น NULL ได้
• CTE = ความอ่านง่าย ไม่ใช่รั้วประสิทธิภาพอัตโนมัติหรือการันตีว่า materialize ตรวจ EXPLAIN เมื่อสำคัญ
• ทุก WITH RECURSIVE ที่เดินข้อมูลที่คุณควบคุมไม่ได้ควรมีจุดหยุด: CYCLE, ตัวนับความลึกกับ WHERE depth < n หรือ UNION แทน UNION ALL
• CTE ที่แก้ข้อมูลเห็น snapshot เดียวกันหมดและรันไม่ว่า query หลักจะอ่านมันหรือไม่

🐘 Subquery หรือ CTE?

ใช้ subquery เมื่อขั้นตอนนั้นเล็กและใช้ครั้งเดียว ใช้ CTE เมื่ออยากตั้งชื่อขั้นตอน ใช้ซ้ำ หรืออ่าน query จากบนลงล่าง ถ้า query มีวงเล็บซ้อนสามสี่ชั้น ถึงเวลาใช้ CTE

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
subquery แบบ inline	กระชับ planner เห็นทุกอย่าง	ซ้อนลึกอ่านและ debug ยาก
CTE (inline โดยค่าเริ่มต้น)	อ่านจากบนลงล่าง plan เหมือน subquery เมื่อใช้ครั้งเดียว	อ้างถึงสองครั้งจะ materialize และกันการผลักตัวกรองด้านนอกลงไป
AS MATERIALIZED	คำนวณขั้นตอนแพงครั้งเดียว คาดเดาได้	เป็นรั้วกั้นการ optimize: ตัวกรองด้านนอกทำทีหลัง
correlated scalar subquery	เขียนตรงไปตรงมามาก	probe ต่อ row ด้านนอก (165 ms เทียบกับ 51 ms ของ join ข้างบน)
WITH RECURSIVE	เดินต้นไม้และกราฟใน SQL	ต้องมีตัวป้องกันไม่จบ; อาจช้าบนกราฟใหญ่

Common Misconceptions

• “EXISTS เร็วกว่า IN” ทั้งสองคอมไพล์เป็น semi join เดียวกัน (plan และ cost เหมือนกันข้างบน)
• “NOT IN กับ NOT EXISTS เหมือนกัน” ต่างกันทันทีที่ subquery คืน NULL: NOT IN ไม่คืน row เลย
• “CTE ถูก materialize เสมอ” ไม่ใช่ตั้งแต่ PostgreSQL 12: CTE ที่อ้างถึงครั้งเดียวถูก inline
• “CTE ถูก inline เสมอ” อ้างถึงสองครั้งมันถูก materialize; AS MATERIALIZED บังคับได้
• “recursive CTE วนไม่รู้จบเมื่อเจอ cycle” วนถ้าไม่ป้องกัน; CYCLE ป้องกันให้เอง

scalar subquery ต้องคืนอะไร?

กี่ row ก็ได้ หนึ่ง row หนึ่ง column พอดี เฉพาะตัวเลข ทั้ง table

SELECT name FROM authors WHERE id NOT IN (SELECT author_id FROM books) ไม่คืน row เลย ทั้งที่ Sela Vance ไม่มีหนังสือ เพราะอะไร?

PostgreSQL 18 ไม่รองรับ NOT IN books.author_id มี NULL จึงทำให้ NOT IN ไม่เคยเป็นจริง subquery เป็น correlated authors ไม่มี index บน id

สำหรับ "ผู้เขียนที่มีหนังสืออย่างน้อยหนึ่งเล่ม" IN (subquery) กับ EXISTS (subquery) เทียบกันอย่างไรใน PostgreSQL?

EXISTS เร็วกว่ามากเสมอ IN เร็วกว่ามากเสมอ planner แปลงทั้งสองเป็น semi join; plan และ cost เหมือนกันในบทเรียน คืน row ต่างกัน

อะไรทำให้ CTE ไม่ถูก inline เข้า query ด้านนอก?

การตั้งชื่อ อ้างถึงสองครั้ง หรือเขียน AS MATERIALIZED ใช้ SELECT * ใน CTE วางไว้ก่อนใน list ของ WITH

อะไรป้องกันการเดิน WITH RECURSIVE ไม่ให้วนไม่รู้จบบนข้อมูลที่มี cycle?

ไม่มี PostgreSQL ตรวจจับเองอัตโนมัติ clause CYCLE (หรือจำกัดความลึก หรือ UNION แทน UNION ALL) เพิ่ม ORDER BY ใช้ LIMIT ในส่วน non-recursive

Submit

Mark as complete