Subquery และ CTE

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

เรายังคงใช้ table authors, books, และ sales ต่อไป

Scalar subquery

scalar subquery คืนค่าเพียงค่าเดียวเป๊ะ ๆ คุณจึงวางไว้ตรงไหนก็ได้ที่ต้องการค่าเดียว — รวมถึงในการเปรียบเทียบ ที่นี่เราแสดงรายการหนังสือที่ตีพิมพ์หลังจากปีที่ตีพิมพ์โดยเฉลี่ย

psql

SELECT title, published
FROM books
WHERE published > (SELECT avg(published) FROM books)
ORDER BY published;

Node.js

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

Python

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

Go

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

Rust

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

      title       | published
------------------+-----------
 Tidal Notes      |      2011
 The Glass Orchard|      2018
(2 rows)

SELECT avg(published) ชั้นในผลิตตัวเลขออกมาตัวเดียว จากนั้น query ชั้นนอกก็เอาหนังสือแต่ละเล่มไปเทียบกับตัวเลขนั้น

IN และ EXISTS subquery

เมื่อ query ชั้นในคืนรายการของค่ากลับมา IN จะตรวจว่า column ตรงกับค่าใดในรายการนั้นหรือไม่ ส่วน EXISTS ต่างออกไปนิดเดียวแต่สำคัญ คือคืนค่าจริงทันทีที่ subquery ผลิต row ออกมาอย่างน้อยหนึ่ง row จึงเข้ากับคำถามแบบ “มี row ที่เกี่ยวข้องอยู่ไหม?” ที่นี่เราหาผู้เขียนที่มียอดขายบันทึกไว้อย่างน้อยหนึ่งรายการ

psql

SELECT a.name
FROM authors AS a
WHERE EXISTS (
  SELECT 1
  FROM books AS b
  JOIN sales AS s ON s.book_id = b.id
  WHERE b.author_id = a.id
)
ORDER BY a.name;

Node.js

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

Python

cur.execute("""
    SELECT a.name
    FROM authors AS a
    WHERE EXISTS (
      SELECT 1 FROM books AS b
      JOIN sales AS s ON s.book_id = b.id
      WHERE b.author_id = a.id
    )
    ORDER BY a.name
""")
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
  SELECT a.name
  FROM authors AS a
  WHERE EXISTS (
    SELECT 1 FROM books AS b
    JOIN sales AS s ON s.book_id = b.id
    WHERE b.author_id = a.id
  )
  ORDER BY a.name`)

Rust

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

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

นี่คือ subquery แบบ correlated: query ชั้นในอ้างถึง a.id จาก query ชั้นนอก จึงเปรียบเสมือนถูกประเมินใหม่สำหรับผู้เขียนแต่ละคน SELECT 1 เป็นธรรมเนียมปฏิบัติ — กับ EXISTS สิ่งที่สำคัญคือมี row อยู่หรือไม่ ไม่ใช่ค่าข้างใน เราจึง select ค่าคงที่ตัวหนึ่งออกมา

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

CTE นิยามผลลัพธ์ชั่วคราวที่มีชื่ออยู่ด้านบนสุดของ query ของคุณด้วย WITH แล้วให้คุณอ้างถึงด้วยชื่อนั้นในส่วนล่าง วิธีนี้ไม่เปลี่ยนสิ่งที่คำนวณ แต่ช่วยคลายปม query ที่ซับซ้อน ที่นี่เรารวมยอดขายต่อหนังสือก่อน แล้วจึง join ยอดรวมเหล่านั้นกลับเข้าไปเพื่อหาชื่อของหนังสือแต่ละเล่มและตัวเลขที่พร้อมจัดอันดับ

psql

WITH book_sales AS (
  SELECT book_id, sum(copies) AS total
  FROM sales
  GROUP BY book_id
)
SELECT b.title, bs.total
FROM book_sales AS bs
JOIN books AS b ON b.id = bs.book_id
ORDER BY bs.total DESC;

Node.js

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

Python

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

Go

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

Rust

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

      title       | total
------------------+-------
 The Glass Orchard|  1200
 Tidal Notes      |   450
 Quiet Harbors    |   200
(3 rows)

ชื่อ book_sales อ่านเหมือนตัวแปรที่เก็บยอดรวมต่อหนังสือเอาไว้ คุณนิยาม CTE ได้หลายตัวใน WITH เดียว โดยคั่นด้วยจุลภาค และแต่ละตัวอ้างถึงตัวก่อนหน้าได้

ชิมลาง WITH RECURSIVE

CTE แบบ WITH RECURSIVE อ้างถึงตัวเองได้ จึงเดินตามโซ่หรือต้นไม้ได้ — แผนผังองค์กร, ลำดับชั้นของหมวดหมู่, หรือในที่นี้คือลำดับตัวเลขง่าย ๆ คุณคงไม่ได้ใช้บ่อยนัก แต่นี่คือเครื่องมือสำหรับข้อมูลแบบลำดับชั้น

psql

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

Node.js

const res = await pool.query(`
  WITH RECURSIVE counter AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 5
  )
  SELECT n FROM counter
`);

Python

cur.execute("""
    WITH RECURSIVE counter AS (
      SELECT 1 AS n
      UNION ALL
      SELECT n + 1 FROM counter WHERE n < 5
    )
    SELECT n FROM counter
""")
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
  WITH RECURSIVE counter AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 5
  )
  SELECT n FROM counter`)

Rust

let rows = sqlx::query(
    "WITH RECURSIVE counter AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 FROM counter WHERE n < 5
     )
     SELECT n FROM counter",
)
.fetch_all(&pool)
.await?;

 n
---
 1
 2
 3
 4
 5
(5 rows)

SELECT แรกคือจุดเริ่มต้น และส่วนที่อยู่หลัง UNION ALL ก็กิน row ที่ผลิตมาแล้วจนถึงตอนนี้ ไปเรื่อย ๆ จนกระทั่งเงื่อนไข WHERE หยุดเพิ่ม row ใหม่ recursive CTE มีบทเจาะลึกเป็นของตัวเองในคอร์สนี้ในภายหลัง

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

• scalar subquery ต้องคืนค่าเพียงหนึ่ง row หนึ่ง column เป๊ะ ๆ ถ้าคืนมากกว่านั้น PostgreSQL จะโยน error ออกมาตอนรันไทม์
• CTE ให้คุณตั้งชื่อแต่ละขั้นตอน query ที่ปกติจะซ้อนกันสามชั้นจึงกลายเป็นลำดับจากบนลงล่างที่อ่านง่าย
• subquery แบบ correlated อ้างถึง column จาก query ชั้นนอกและเปรียบเสมือนถูกรันครั้งหนึ่งต่อหนึ่ง row ชั้นนอก ส่วนแบบ uncorrelated รันเพียงครั้งเดียว EXISTS มักเป็นแบบ correlated เกือบทุกครั้ง
• เลือก EXISTS แทน IN เมื่อคุณสนใจแค่ว่ามีคู่ที่ตรงกันอยู่หรือไม่ โดยเฉพาะกับ table ขนาดใหญ่ เพราะ EXISTS หยุดได้ที่ row แรกที่ตรงกัน
• ให้ query WITH RECURSIVE มีเงื่อนไขสิ้นสุดเสมอ ไม่อย่างนั้นจะวนสร้าง row ไปไม่รู้จบ

🐘 subquery หรือ CTE?

ทั้งสองมักคำนวณสิ่งเดียวกัน เลือก subquery สำหรับ expression เล็ก ๆ ที่ใช้ครั้งเดียวอย่าง (SELECT avg(...)) และเลือก CTE เมื่อขั้นตอนนั้นถูกใช้ซ้ำ หรือเมื่อการตั้งชื่อขั้นตอนนั้นทำให้ทั้ง query อ่านง่ายขึ้น ความอ่านง่ายเป็นฟีเจอร์จริง ๆ — ตัวคุณในอนาคตที่ต้องดูแล query นี้จะขอบคุณคุณ

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

ตัวเลือก	Benefit	Cost
CTE (WITH ...)	ตั้งชื่อขั้นตอนได้ อ่านง่าย รีวิวง่าย	เพิ่มบรรทัดโค้ด และในเวอร์ชันเก่าเคยถูก materialize แยกจาก query หลัก
Subquery แบบฝังตรง (inline)	เขียนสั้น บาง query planner จัดการได้ดีกว่า	ซ้อนลึกแล้วอ่านยาก โดยเฉพาะเมื่อมีหลายชั้น
Correlated subquery	ยืดหยุ่น เขียนตามโจทย์ทีละ row ได้	รันซ้ำต่อ row ฝั่งนอก ช้าบน table ใหญ่ เมื่อเทียบกับ join

ข้อผิดพลาดที่พบบ่อย

• คิดว่า CTE ถูก materialize เป็น “fence” เสมอ — พฤติกรรมนี้เปลี่ยนไปแล้ว PostgreSQL เวอร์ชันปัจจุบัน inline CTE เข้ากับ query หลักได้เหมือน subquery ธรรมดา อย่ายึดติดกับความเข้าใจเก่า
• เขียน correlated subquery ทั้งที่ join ทำงานเดียวกันได้เร็วกว่า — เพราะ correlated subquery รันซ้ำต่อทุก row ฝั่งนอก ถ้าโจทย์แค่ต้องการ join ข้อมูล ให้ใช้ join หรือ window function แทน
• ซ้อน subquery ลึกหลายชั้นจนอ่านไม่ออก — เมื่อ query เริ่มมีวงเล็บซ้อนสามสี่ชั้น ให้แตกเป็น CTE ที่ตั้งชื่อแต่ละขั้นตอน จะ debug และรีวิวง่ายกว่ามาก

💡 ตัวอย่างจากของจริง

GitLab — query สำหรับรายงานภายใน (internal reporting) มักใช้ CTE หลายตัวต่อกันเพื่อแตกโจทย์ซับซ้อนเป็นขั้นตอนที่มีชื่อ ทำให้ทีมรีวิว SQL อ่านตามลำดับได้โดยไม่ต้องไล่วงเล็บ

ทีม data/analytics — เวลาสร้างรายงานสรุปหลายชั้น (เช่น หา metric กลาง แล้วกรองต่อจาก metric นั้น) ทีมข้อมูลมักเลือก CTE แทน subquery ซ้อนกันเพื่อให้ query อธิบายตัวเองได้

scalar subquery ที่ใช้ในการเปรียบเทียบต้องคืนค่าอะไร?

รายการของค่า หนึ่ง row หนึ่ง column เป๊ะ ๆ อย่างน้อยหนึ่ง row ค่า boolean จริงหรือเท็จ

CTE ที่นิยามด้วย WITH ให้อะไรกับคุณ?

table ถาวรที่เก็บไว้บนดิสก์ ผลลัพธ์ชั่วคราวที่มีชื่อซึ่งคุณอ้างถึงได้ภายใน query เดียวกัน index บน table ที่อยู่เบื้องล่าง query ที่เร็วขึ้นในทุกกรณี

EXISTS เหมาะเป็นพิเศษเมื่อใด?

เมื่อคุณต้องการรวมค่าของ column เมื่อคุณสนใจแค่ว่ามี row ที่ตรงกันอย่างน้อยหนึ่ง row อยู่หรือไม่ เมื่อคุณต้องการคืน column จาก query ชั้นใน เมื่อ subquery ต้องคืนหลาย column

Submit

Mark as complete