Sebuah CTE (Common Table Expression), didefinisikan dengan WITH, adalah sebuah named temporary result set yang dapat Anda referensikan dalam sebuah query. CTEs membuat query kompleks lebih readable dengan memecahnya menjadi langkah-langkah yang bernama dan logis — dan mereka mengaktifkan recursive queries (untuk hierarchies).
active_users (
id, name users active
)
active_users
name ;
WITH ... AS (...) mendefinisikan sebuah CTE — sebuah named query yang Anda referensikan nanti. Ini seperti sebuah subquery, tetapi bernama dan readable, terutama untuk query kompleks.
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
Multiple CTEs memecah sebuah query kompleks menjadi langkah-langkah yang readable dan sequential — masing-masing membangun di atas yang sebelumnya. Ini jauh lebih jelas daripada deeply nested subqueries.
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.
-- 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) melintasi data hierarchical/tree (org charts, category trees, bill-of-materials, graph paths) — sesuatu yang sebaliknya sangat sulit dalam SQL.
CTEs adalah sebuah fitur SQL penting untuk readability dan recursion, dan memahami mereka bernilai untuk menulis complex queries yang maintainable dan untuk menangani hierarchical data.
Manfaat sehari-hari utama adalah readability: complex queries dengan multiple levels subqueries menjadi sulit dibaca dan dipelihara, sementara CTEs memungkinkan Anda memecah query menjadi named, sequential logical steps (dibaca top-to-bottom seperti serangkaian operasi), secara dramatis meningkatkan clarity — karena alasan ini, CTEs sering lebih dipilih daripada deeply nested subqueries untuk complex queries, menjadikan mereka alat penting untuk menulis maintainable SQL.
Equally penting adalah recursive CTE capability (WITH RECURSIVE), yang mengaktifkan querying hierarchical dan tree-structured data (organizational charts, category trees, bill-of-materials, graph traversal) — sebuah class queries yang sebaliknya sangat sulit atau tidak mungkin dalam standard SQL, menjadikan recursive CTEs solusi standar untuk hierarchical data traversal.
Memahami kedua uses — CTEs untuk mendekomposisi complex queries menjadi readable steps, dan recursive CTEs untuk hierarchical traversal — mencakup practical value.
Karena complex queries adalah common dalam real applications (dan readability/maintainability penting), dan karena hierarchical data (yang recursive CTEs secara unik menangani dengan baik) muncul frequently (org structures, nested categories, threaded data), memahami CTEs adalah valuable senior-level knowledge yang meningkatkan query maintainability dan membuka hierarchical querying — sebuah frequently-relevant tool untuk menulis clean, sophisticated SQL dan sebuah common topic yang mendemonstrasikan advanced SQL capability, particularly recursive form untuk tree-structured data.