Kursus MariaDB / Memecah Kueri Menjadi Tahap dengan WITH dan CTE

Common table expression atau CTE adalah hasil kueri antara yang diberi nama dengan WITH dan berlaku dalam satu perintah SQL. CTE membantu memisahkan kondisi serta agregasi yang rumit menjadi tahap yang mudah dibaca, tetapi tidak membuat tabel permanen atau otomatis menjamin eksekusi lebih cepat.

Berikan satu tugas pada setiap hasil antara

Memilih pesanan dibayar, menghitung nilai barang per pesanan, dan menambahkan nama pelanggan dalam satu ekspresi bertingkat dapat menyamarkan kondisi serta arti satu baris. Pada contoh ini, paid_orders mewakili pesanan yang memenuhi syarat dan order_totals mewakili nilai barang per pesanan. Setiap nama harus mempunyai kolom, kunci, dan arti baris yang jelas.

Hasil Arti satu baris Kolom utama
paid_orders Satu pesanan dibayar order_id, customer_id
order_totals Total barang satu pesanan order_id, goods_total
SELECT akhir Satu pesanan yang memenuhi syarat Nama pelanggan dan nilai barang

Nama yang deskriptif memudahkan pemeriksaan, tetapi tidak mengubah jumlah atau arti baris dengan sendirinya. Ringkasan tetap memerlukan GROUP BY yang sesuai. Referensi WITH MariaDB menjelaskan definisi serta cakupan CTE.

Siapkan data pesanan yang dapat dijalankan sendiri

Hubungkan ke server belajar sebagai course_user dan pilih shopcourse. Panduan klien dan berkas SQL UTF-8 menjelaskan penyimpanan kode serta pelaksanaan dengan SOURCE C:/mariadb-lab/lesson.sql;. Ganti jalur dengan berkas sebenarnya dan jalankan SQL di dalam klien MariaDB.

Sebelum membuat c31_customers, c31_products, c31_orders, dan c31_items, periksa SELECT VERSION(), DATABASE(); serta SHOW TABLES;. Jika objek sudah ada, periksa definisi dan barisnya dahulu. Gunakan hanya tabel latihan yang boleh dibuang; jangan mengganti tabel aplikasi. Periksa pula dukungan sintaks WITH pada versi server yang digunakan.

USE shopcourse;
CREATE TABLE c31_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c31_order_customer FOREIGN KEY (customer_id)
  REFERENCES c31_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c31_item_order FOREIGN KEY (order_id)
  REFERENCES c31_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c31_item_product FOREIGN KEY (product_id)
  REFERENCES c31_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c31_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c31_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c31_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c31_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

SQL mempertahankan nama dan kota Korea dari sumber, sedangkan penjelasan memakai ID untuk perbandingan. Produk 1–4 adalah buku catatan, pena, cangkir, dan lampu. Pelanggan 1 serta 3 berada di Seoul, pelanggan 2 di Busan, dan pelanggan 4 di Daejeon. Pertahankan teks contoh dengan UTF-8.

Ada tiga pesanan dibayar dan satu dibatalkan. Merangkum rincian per pesanan terlebih dahulu memudahkan pelacakan masalah penggandaan saat JOIN. Blok persiapan menyertakan semua tabel sehingga tidak bergantung pada latihan sebelumnya.

Definisikan beberapa CTE sebelum SELECT akhir

WITH paid_orders AS (
 SELECT order_id,customer_id FROM c31_orders WHERE status='paid'
), order_totals AS (
 SELECT order_id,SUM(quantity*unit_price) AS goods_total
 FROM c31_items GROUP BY order_id
)
SELECT p.order_id,c.customer_name,t.goods_total
FROM paid_orders p JOIN order_totals t ON t.order_id=p.order_id
JOIN c31_customers c ON c.customer_id=p.customer_id
ORDER BY p.order_id;
Pesanan ID pelanggan Total barang
101 1 11500.00
102 1 8000.00
103 2 19500.00

Tulis WITH satu kali pada awal, pisahkan definisi bernama dengan koma, dan tempatkan setiap SELECT dalam tanda kurung sesudah AS. SELECT akhir mengikuti seluruh definisi. Titik koma di tengah mengakhiri perintah sehingga nama CTE tidak tersedia untuk kueri berikutnya.

paid_orders memilih pesanan berdasarkan status, order_totals merangkum rincian berdasarkan order_id, dan SELECT akhir menghubungkan kedua hasil dengan pelanggan. Pelanggan 1 tetap mempunyai dua baris karena hasil akhirnya satu baris per pesanan. Mengubah laporan menjadi satu baris per pelanggan membutuhkan pengelompokan tambahan yang sesuai.

Periksa baris antara sebelum hasil akhir

Jika jumlah tidak sesuai, jalankan SELECT di dalam setiap CTE secara terpisah. paid_orders seharusnya memuat ID 101, 102, dan 103. order_totals yang belum difilter dapat memuat empat total, termasuk 1500 untuk pesanan dibatalkan 104. Pembatasan menjadi pesanan dibayar baru terjadi ketika hasil-hasil tersebut digabungkan pada SELECT akhir.

WITH paid_orders AS (
 SELECT order_id,customer_id FROM c31_orders WHERE status='paid'
)
SELECT * FROM paid_orders ORDER BY order_id;

Cara lain adalah mempertahankan definisi CTE dan sementara mengganti SELECT akhir untuk memeriksa hasilnya. Jika hanya filter paid diganti menjadi canceled pada contoh lengkap, hasil menjadi pesanan 104 milik pelanggan 3 dengan total 1500. Pulihkan paid untuk memperoleh kembali tiga pesanan awal.

Penggabungan dengan order_totals menggunakan INNER JOIN sehingga pesanan yang tidak memiliki rincian tidak tampil. Jika pesanan kosong perlu dipertahankan, tentukan LEFT JOIN serta kebijakan total yang hilang. Panduan LEFT JOIN menjelaskan pengaruh pemilihan sisi yang dipertahankan.

Pahami cakupan satu perintah dan pilihan pengoptimal

CTE hanya berlaku selama perintah SQL yang mendefinisikannya. Menjalankan SELECT dari paid_orders sesudah perintah selesai tidak membaca hasil sebelumnya karena tidak ada tabel permanen dengan nama tersebut. Untuk definisi kueri yang dipakai lintas perintah, pertimbangkan VIEW; untuk menyimpan baris sementara, pertimbangkan rancangan tabel sementara yang sesuai.

Pengoptimal dapat menggabungkan atau mematerialisasi struktur kueri antara sesuai kueri dan versi server. WITH tidak menjanjikan satu kali evaluasi atau eksekusi lebih cepat. Nilai kemudahan pemeliharaan terpisah dari kinerja dan periksa EXPLAIN ketika kinerja menjadi masalah. CTE rekursif yang merujuk dirinya sendiri merupakan topik berbeda dari pemisahan nonrekursif di sini.

Periksa nama, kolom, dan tingkat agregasi

Untuk kesalahan sintaks, periksa tanda kurung, koma, dan titik koma dahulu. Jika kolom tidak ditemukan, periksa daftar kolom yang dihasilkan SELECT CTE. Gunakan nama unik dan daftar kolom yang jelas agar tidak menyamarkan tabel dasar. CTE berikutnya dapat memakai CTE yang sudah didefinisikan sebelumnya dalam WITH yang sama.

Tidak ada objek CTE permanen yang perlu dihapus; hanya tabel latihan dasar yang dibersihkan. Sebelum mengulang, periksa basis data yang dipilih, SHOW CREATE TABLE, serta isi keempat tabel dan cadangkan data yang perlu dipertahankan. Hapus hanya objek c31_ milik artikel ini, dimulai dari tabel anak lalu induk. DROP TABLE menghapus struktur beserta data serta tidak dibalik dengan ROLLBACK biasa; pisahkan pembersihan dari percobaan transaksi.

DROP TABLE shopcourse.c31_items;
DROP TABLE shopcourse.c31_orders;
DROP TABLE shopcourse.c31_products;
DROP TABLE shopcourse.c31_customers;