Common Table Expressions (CTEs) คืออะไร?

Question

Accepted Answer

**CTE** (Common Table Expression) ซึ่งนิยามด้วย **`WITH`** คือชุดผลลัพธ์ชั่วคราวที่มีชื่อซึ่งคุณสามารถอ้างอิงภายใน query ได้ CTEs ทำให้ query ที่ซับซ้อน **อ่านง่ายขึ้น** ด้วยการแยกออกเป็นขั้นตอนเชิงตรรกะที่มีชื่อ — และเปิดทางให้ทำ query แบบ **recursive** (สำหรับลำดับชั้น)

## CTE พื้นฐาน — ตั้งชื่อ subquery เพื่อความอ่านง่าย

```sql
WITH active_users AS (              -- define a named temporary result set
    SELECT id, name FROM users WHERE active = true
)
SELECT * FROM active_users          -- reference it like a table
WHERE name LIKE 'A%';
```

`WITH ... AS (...)` นิยาม CTE — query ที่มีชื่อซึ่งคุณอ้างอิงในภายหลัง มันคล้าย subquery แต่ **มีชื่อและอ่านง่าย** โดยเฉพาะสำหรับ query ที่ซับซ้อน

## CTEs หลายตัว — การร้อยขั้นตอนเชิงตรรกะ

```sql
WITH
  monthly_sales AS (
    SELECT month, SUM(amount) AS total FROM orders GROUP BY month
  ),
  avg_sales AS (
    SELECT AVG(total) AS avg_total FROM monthly_sales   -- a CTE can reference another
  )
SELECT m.month, m.total
FROM monthly_sales m, avg_sales a
WHERE m.total > a.avg_total;        -- months above the average
```

CTEs หลายตัวแยก query ที่ซับซ้อนออกเป็นขั้นตอนต่อเนื่องที่อ่านง่าย — แต่ละขั้นต่อยอดจากขั้นก่อนหน้า ซึ่งชัดเจนกว่า subqueries ที่ซ้อนลึกมาก

## CTEs กับ subqueries (ความอ่านง่าย)

```text
Nested subqueries → hard to read when deeply nested
CTEs → name each step, read top-to-bottom like a sequence → much clearer
→ CTEs are often preferred for COMPLEX queries purely for readability/maintainability.
```

## Recursive CTEs — สำหรับลำดับชั้น

```sql
-- traverse a hierarchy (e.g. all descendants of a category, an org chart)
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1   -- base case (the top)
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id               -- recursive step
)
SELECT * FROM subordinates;          -- the whole tree under employee 1
```

**Recursive CTEs** (`WITH RECURSIVE`) ท่องไปในข้อมูลแบบลำดับชั้น/ต้นไม้ (แผนผังองค์กร, ต้นไม้หมวดหมู่, bill-of-materials, เส้นทางกราฟ) — สิ่งที่ทำได้ยากมากใน SQL ในวิธีอื่น

## ทำไมจึงสำคัญ

CTEs เป็นฟีเจอร์ SQL ที่สำคัญสำหรับ **ความอ่านง่ายและ recursion** และการเข้าใจมันมีคุณค่าต่อการเขียน query ที่ซับซ้อนซึ่งดูแลรักษาได้และต่อการจัดการข้อมูลแบบลำดับชั้น

ประโยชน์หลักในทุกวันคือ **ความอ่านง่าย**: query ที่ซับซ้อนซึ่งมี subqueries หลายระดับจะอ่านและดูแลรักษายาก ในขณะที่ CTEs ช่วยให้คุณแยก query ออกเป็นขั้นตอนเชิงตรรกะที่มีชื่อและต่อเนื่อง (อ่านจากบนลงล่างเหมือนชุดของการดำเนินการ) เพิ่มความชัดเจนอย่างมาก — ด้วยเหตุนี้ CTEs จึงมักได้รับความนิยมมากกว่า subqueries ที่ซ้อนลึกสำหรับ query ที่ซับซ้อน ทำให้เป็นเครื่องมือสำคัญสำหรับการเขียน SQL ที่ดูแลรักษาได้

สิ่งที่สำคัญพอ ๆ กันคือความสามารถของ **recursive CTE** (`WITH RECURSIVE`) ซึ่งเปิดทางให้ query **ข้อมูลแบบลำดับชั้นและโครงสร้างต้นไม้** (แผนผังองค์กร, ต้นไม้หมวดหมู่, bill-of-materials, การท่องกราฟ) — กลุ่มของ query ที่ทำได้ยากมากหรือเป็นไปไม่ได้ใน SQL มาตรฐาน ทำให้ recursive CTEs เป็นทางออกมาตรฐานสำหรับการท่องข้อมูลแบบลำดับชั้น

การเข้าใจการใช้งานทั้งสอง — CTEs สำหรับการแยก query ที่ซับซ้อนออกเป็นขั้นตอนที่อ่านง่าย และ recursive CTEs สำหรับการท่องลำดับชั้น — ครอบคลุมคุณค่าในทางปฏิบัติ

เนื่องจาก query ที่ซับซ้อนพบได้บ่อยในแอปพลิเคชันจริง (และความอ่านง่าย/การดูแลรักษามีความสำคัญ) และเนื่องจากข้อมูลแบบลำดับชั้น (ซึ่ง recursive CTEs จัดการได้ดีโดยเฉพาะ) ปรากฏบ่อย (โครงสร้างองค์กร, หมวดหมู่ซ้อน, ข้อมูลแบบเธรด) การเข้าใจ CTEs จึงเป็นความรู้ระดับ senior ที่มีคุณค่าซึ่งช่วยปรับปรุงการดูแลรักษา query และปลดล็อกการ query แบบลำดับชั้น — เป็นเครื่องมือที่เกี่ยวข้องบ่อยสำหรับการเขียน SQL ที่สะอาดและซับซ้อน และเป็นหัวข้อที่พบบ่อยซึ่งแสดงถึงความสามารถ SQL ขั้นสูง โดยเฉพาะรูปแบบ recursive สำหรับข้อมูลโครงสร้างต้นไม้