Gunakan Ekspresi Tabel Umum

  • 4 menit

Common Table Expressions (CTEs) menyediakan mekanisme bagi Anda untuk mendefinisikan subquery yang kemudian dapat digunakan di tempat lain dalam kueri. Tidak seperti tabel turunan, CTE didefinisikan di awal kueri dan dapat direferensikan beberapa kali di kueri luar.

CTE adalah ekspresi bernama yang didefinisikan dalam kueri. Seperti subquery dan tabel turunan, CTE menyediakan sarana untuk memecah masalah query menjadi unit yang lebih kecil dan lebih modular. CTE terbatas dalam lingkup eksekusi kueri luar. Ketika kueri luar berakhir, begitu juga masa pakai CTE.

Tulis kueri dengan CTE untuk mengambil hasil

Anda dapat menggunakan CTE untuk mengambil hasil. Untuk membuat CTE, Anda mendefinisikannya dalam klausa WITH, berdasarkan sintaks berikut:

WITH <CTE_name>
AS (<CTE_definition>)

Misalnya, untuk menggunakan CTE untuk mengambil informasi tentang pesanan yang dilakukan per tahun oleh pelanggan yang berbeda, Anda dapat menjalankan kueri berikut:

WITH CTE_year 
AS
(
    SELECT YEAR(orderdate) AS orderyear, custid
    FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM CTE_year
GROUP BY orderyear;

Anda memberi nama CTE (bernama CTE_year) menggunakan klausa WITH, lalu Anda menggunakan AS () untuk mendefinisikan subquery Anda. Anda kemudian dapat mereferensikan CTE yang dihasilkan di kueri luar, yang dalam hal ini dilakukan dalam pernyataan SELECT akhir (FROM CTE_year). Hasilnya akan terlihat seperti ini:

tahun pesanan cust_count
2019 67
2020 86
2021 81

Saat menulis kueri dengan CTE, pertimbangkan panduan berikut:

  • CTE memerlukan nama untuk ekspresi tabel, selain nama unik untuk setiap kolom yang dirujuk dalam klausa SELECT CTE.
  • CTE dapat menggunakan alias sebaris atau eksternal untuk kolom.
  • Tidak seperti tabel turunan, CTE dapat direferensikan beberapa kali dalam kueri yang sama dengan satu definisi. Beberapa CTE juga dapat didefinisikan dalam klausa WITH yang sama.
  • CTE mendukung rekursi, di mana ekspresi didefinisikan dengan referensi ke dirinya sendiri. CTE rekursif berada di luar lingkup modul ini.