Kursus MariaDB / Menulis Kondisi dengan Subkueri, EXISTS, dan NOT EXISTS
Subkueri menyediakan nilai atau kumpulan baris di dalam perintah SQL lain. EXISTS dan NOT EXISTS memeriksa ada atau tidaknya baris terkait, sehingga cocok untuk memilih setiap pelanggan satu kali atau mencari pelanggan tanpa pesanan dibayar.
Pilih nilai tunggal, daftar, atau pengujian keberadaan
Perbandingan harga produk dengan rata-rata membutuhkan satu angka. Pemilihan pelanggan yang pernah mempunyai pesanan dibayar hanya membutuhkan bukti bahwa pesanan tersebut ada. Pilih bentuk hasil sesuai pertanyaan dan pertahankan arti baris kueri luar, bukan memakai satu bentuk subkueri untuk semua kebutuhan.
| Bentuk | Hasil yang diperlukan | Hal yang diperiksa |
|---|---|---|
| Subkueri skalar | Satu nilai dari satu kolom | Lebih dari satu baris merupakan kesalahan; tanpa baris menghasilkan NULL |
| Subkueri IN | Daftar nilai | Periksa nilai NULL pada daftar dan nilai luar |
| EXISTS | Ada baris terkait yang memenuhi syarat | Sertakan kunci korelasi yang benar |
| NOT EXISTS | Tidak ada baris terkait yang memenuhi syarat | Tentukan secara tepat apa yang harus tidak ada |
Panduan perbandingan NULL menjelaskan mengapa NOT IN perlu diperiksa ketika daftar memuat NULL. Pengujian keberadaan menyeleksi baris luar, sedangkan penggabungan hasil secara vertikal dengan UNION mempunyai tujuan berbeda. Referensi EXISTS MariaDB menjelaskan pengujian keberadaan baris.
Siapkan riwayat pelanggan yang berbeda
Hubungkan ke server belajar sebagai course_user dan pilih shopcourse. Panduan klien dan berkas SQL UTF-8 menjelaskan cara menyimpan kode serta menjalankannya menggunakan SOURCE C:/mariadb-lab/lesson.sql;. Ganti jalur dengan berkas sebenarnya dan jalankan SQL di dalam klien MariaDB.
Sebelum membuat c29_customers, c29_products, c29_orders, dan c29_items, periksa SELECT VERSION(), DATABASE(); serta SHOW TABLES;. Jika objek sudah ada, periksa definisi dan barisnya terlebih dahulu. Gunakan hanya objek latihan yang boleh dibuang dan jangan mengganti tabel aplikasi. Blok berikut menyertakan seluruh persiapan agar artikel dapat dijalankan sendiri.
USE shopcourse;
CREATE TABLE c29_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 c29_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 c29_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_c29_order_customer FOREIGN KEY (customer_id)
REFERENCES c29_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c29_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_c29_item_order FOREIGN KEY (order_id)
REFERENCES c29_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c29_item_product FOREIGN KEY (product_id)
REFERENCES c29_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c29_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c29_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c29_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 c29_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 contoh nama dan kota Korea dari sumber. 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. Simpan teks dengan UTF-8 dan gunakan ID untuk membandingkan hasil.
Pelanggan 1 mempunyai dua pesanan dibayar, pelanggan 2 satu pesanan dibayar, pelanggan 3 hanya pesanan dibatalkan, dan pelanggan 4 tidak mempunyai pesanan. Contoh berikut memilih pelanggan satu kali, tanpa menghasilkan baris terpisah untuk setiap pesanan yang cocok.
Bandingkan harga dengan hasil agregat skalar
SELECT product_id,product_name FROM c29_products
WHERE price > (SELECT AVG(price) FROM c29_products) ORDER BY product_id;
Rata-rata empat harga produk adalah 9500, sehingga hanya lampu dengan ID 4 yang lebih mahal. AVG mengubah seluruh baris harga menjadi satu nilai. Mengembalikan beberapa harga tanpa agregasi pada posisi skalar ini menyebabkan kesalahan lebih dari satu baris. Tentukan nilai pembanding yang benar daripada menyembunyikan pilihan yang tidak jelas menggunakan LIMIT 1.
Subkueri skalar yang tidak menghasilkan baris bernilai NULL, sekalipun kolom sumber tidak mengizinkan NULL. Perbandingan harga dengan hasil NULL tidak menjadi benar dan tidak memilih produk tersebut. Referensi subkueri skalar MariaDB membahas bentuk hasil dan kondisi ini.
Gunakan EXISTS berkorelasi tanpa menggandakan pelanggan
SELECT c.customer_id,c.customer_name FROM c29_customers c
WHERE EXISTS (SELECT 1 FROM c29_orders o
WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY c.customer_id;
Pelanggan 1 dan 2 masing-masing muncul satu kali. SELECT 1 menyatakan pengujian keberadaan, bukan jumlah atau nilai pesanan. Pesanan kedua pelanggan 1 tidak menggandakan baris pelanggan luar. Bahkan baris hasil subkueri dengan nilai NULL tetap merupakan baris yang ada untuk EXISTS.
Subkueri berkorelasi merujuk kunci dari baris kueri luar. Tanpa o.customer_id=c.customer_id, kueri hanya memeriksa apakah ada pesanan dibayar di seluruh tabel. Karena contoh mempunyai pesanan dibayar, semua pelanggan akan lolos. Hapus hanya kondisi korelasi untuk membandingkan hasil empat pelanggan, lalu pulihkan kondisi agar kembali menjadi pelanggan 1 dan 2.
Penjelasan logis tentang baris luar tidak menentukan strategi eksekusi fisik pengoptimal. Jangan menganggap server pasti membaca ulang seluruh tabel dalam untuk setiap baris luar.
Nyatakan ketiadaan yang dimaksud dengan NOT EXISTS
SELECT c.customer_id,c.customer_name FROM c29_customers c
WHERE NOT EXISTS (SELECT 1 FROM c29_orders o
WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY c.customer_id;
Pelanggan 3 dan 4 tidak mempunyai pesanan dibayar. Pelanggan yang hanya mempunyai pesanan dibatalkan dan pelanggan tanpa pesanan sama sekali sama-sama memenuhi syarat ini. Jika hanya syarat status yang dihapus, pertanyaannya berubah menjadi tidak mempunyai pesanan dengan status apa pun; hasilnya hanya pelanggan 4. Nyatakan arti ketiadaan dengan kalimat sebelum menulis kondisi.
NOT IN dapat menghasilkan perilaku berbeda ketika daftar dalam memuat NULL. Periksa apakah kunci pembanding dan nilai luar mengizinkan NULL serta apakah baris luar bernilai NULL seharusnya termasuk. Jangan menganggap NOT IN selalu setara dengan NOT EXISTS. Memilih EXISTS juga tidak menjamin kueri lebih cepat daripada JOIN.
Periksa bentuk hasil, korelasi, dan rencana eksekusi
Jalankan kueri dalam secara terpisah untuk memeriksa nilai, duplikasi, dan NULL, kemudian hubungkan dengan satu contoh baris luar yang sudah diketahui. Jika semua pelanggan mendapat hasil sama, periksa kondisi korelasinya. Hindari kolom dan pengurutan yang tidak diperlukan dalam pengujian keberadaan sederhana. Nilai kinerja dengan data yang mewakili penggunaan dan rencana eksekusi sebenarnya.
Sebelum mengulang latihan, periksa basis data yang dipilih, SHOW CREATE TABLE, dan isi keempat tabel. Cadangkan data yang perlu dipertahankan. Hapus hanya objek c29_ milik artikel ini, dimulai dari tabel anak lalu tabel induk. DROP TABLE menghapus struktur dan data sekaligus serta tidak dibalik dengan ROLLBACK biasa; pisahkan pembersihan dari percobaan transaksi.
DROP TABLE shopcourse.c29_items;
DROP TABLE shopcourse.c29_orders;
DROP TABLE shopcourse.c29_products;
DROP TABLE shopcourse.c29_customers;




