Belajar SQL Subquery, CASE, UNION, dan CTE: Contoh Query MySQL untuk Pemula

Pernahkah kamu diminta membuat laporan yang tidak cukup diselesaikan dengan SELECT, WHERE, JOIN, atau GROUP BY?

Misalnya, pemilik TokoKita ingin mengetahui produk yang harganya di atas rata-rata seluruh produk. Ia juga ingin mengelompokkan produk berdasarkan tingkat harga, menggabungkan data pelanggan dari dua sumber, serta membuat laporan penjualan yang proses perhitungannya bisa dibaca dan diperiksa dengan mudah.

Semua kebutuhan tersebut dapat diselesaikan dengan teknik SQL yang lebih lanjut.

Pada artikel sebelumnya, kita sudah belajar menggabungkan beberapa tabel menggunakan INNER JOIN dan LEFT JOIN. Sekarang, kita akan melanjutkan perjalanan belajar SQL dengan empat materi penting:

1. Subquery.
2. CASE.
3. UNION dan UNION ALL.
4. Common Table Expression (CTE).

Keempat materi ini akan membantu kita menyusun query yang lebih fleksibel dan terstruktur, terutama ketika mulai mengerjakan kebutuhan Data Analyst, Software Engineer, maupun Data Engineer.

Semua contoh menggunakan sintaks MySQL 8.4.

1. Mengenal Masalah Baru di TokoKita

Bayangkan TokoKita kini sudah memiliki banyak pelanggan dan produk. Tim operasional menggunakan database untuk mencatat transaksi, sementara pemilik bisnis memerlukan laporan untuk mengambil keputusan.

Suatu hari, pemilik toko mengajukan empat pertanyaan.

Pertanyaan pertama: produk apa saja yang memiliki harga lebih tinggi daripada rata-rata harga semua produk?

Pertanyaan kedua: bagaimana mengelompokkan produk menjadi kategori harga murah, menengah, dan mahal?

Pertanyaan ketiga: bagaimana menggabungkan daftar pelanggan lama dengan daftar pelanggan dari sistem baru?

Pertanyaan keempat: bagaimana menyusun laporan penjualan bulanan yang proses perhitungannya bisa dibaca secara bertahap?

Jika hanya menggunakan query sederhana, beberapa kebutuhan tersebut bisa terasa panjang atau sulit dipahami.

Kabar baiknya, SQL menyediakan teknik untuk mengatasinya. Kita tidak harus langsung menggunakan kode aplikasi atau melakukan perhitungan manual di spreadsheet.

Mari kita pelajari satu per satu.

2. Subquery: Query di Dalam Query

2.1. Apa itu Subquery?

Subquery adalah query yang ditempatkan di dalam query lainnya. Query bagian dalam menghasilkan data yang kemudian digunakan oleh query utama.

Bayangkan kita ingin mencari produk dengan harga di atas rata-rata harga seluruh produk TokoKita.

Untuk menjawabnya, kita perlu melakukan dua pekerjaan:

1. Menghitung rata-rata harga produk.
2. Menampilkan produk yang harganya lebih besar daripada rata-rata tersebut.

Kita bisa menjalankan kedua pekerjaan itu dalam satu perintah SQL menggunakan subquery.

Dokumentasi resmi MySQL menjelaskan bahwa subquery merupakan pernyataan SELECT yang berada di dalam pernyataan lain. Hasilnya dapat berupa satu nilai, satu baris, satu kolom, atau kumpulan baris dan kolom, tergantung bentuk subquery yang digunakan.

Referensi: MySQL 8.4 — Subqueries.

2.2. Contoh tabel products

Kita menggunakan tabel produk dari latihan TokoKita sebelumnya.

Anggap seluruh harga tersebut merupakan harga yang tersimpan di database saat ini.

2.3. Menghitung rata-rata harga produk

Sebelum membuat subquery, kita bisa menghitung rata-rata dengan query biasa:

SELECT AVG(price) AS average_price
FROM products;

Hasilnya:

Perhitungannya:

[

\frac{450000+175000+1850000+125000}{4}=650000

]

Jadi, rata-rata harga produk adalah Rp650.000.

Sekarang pertanyaannya: apakah kita harus menghitung rata-rata tersebut secara manual, lalu mengetik angka 650000 ke dalam query lain?

Tidak perlu. Kita dapat menjadikan query rata-rata sebagai subquery.

2.4. Contoh Subquery untuk mencari produk di atas rata-rata

SELECT
    product_name,
    price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Perhatikan bagian berikut:

(
    SELECT AVG(price)
    FROM products
)

Bagian tersebut menghitung rata-rata harga produk. Hasilnya kemudian digunakan oleh query utama pada kondisi WHERE.

Hasil query:

Mengapa hanya Monitor yang muncul?

Karena harga Monitor sebesar Rp1.850.000 lebih tinggi daripada rata-rata harga produk, yaitu Rp650.000.

Kapan Subquery digunakan?

Contoh kebutuhan yang cocok menggunakan subquery:

1. Mencari produk dengan harga di atas rata-rata.
2. Mencari karyawan dengan gaji di atas rata-rata departemen.
3. Membandingkan transaksi dengan nilai transaksi tertinggi.
4. Memeriksa apakah suatu pelanggan memiliki transaksi.
5. Menggunakan hasil perhitungan query lain sebagai kriteria filter.

2.5. Memahami outer query dan inner query

Dalam contoh sebelumnya, ada dua bagian query:

1. Outer query: query utama yang mengambil data produk.
2. Inner query: query di dalam tanda kurung yang menghitung rata-rata harga.

Alur pemahamannya seperti ini:

1. Tentukan nilai rata-rata harga produk.
2. Gunakan nilai tersebut untuk membandingkan harga setiap produk.
3. Tampilkan produk yang memenuhi kondisi.

Ini adalah model berpikir yang berguna ketika membaca subquery yang lebih kompleks.

Namun, penjelasan ini merupakan cara memahami logika query, bukan jaminan bahwa database selalu menjalankan setiap bagian dalam urutan persis seperti yang kita bayangkan. MySQL dapat mengoptimalkan cara query dieksekusi.

2.6. Subquery dengan IN

Sekarang bayangkan pemilik toko ingin melihat produk yang pernah muncul pada detail pesanan.

Kita bisa menggunakan IN untuk membandingkan products.id dengan daftar product_id dari order_items.

SELECT
    id,
    product_name,
    price
FROM products
WHERE id IN (
    SELECT product_id
    FROM order_items
);

Subquery menghasilkan daftar ID produk yang terdapat di detail pesanan. Query utama kemudian mengambil produk yang ID-nya cocok dengan daftar tersebut.

Contoh ini mengasumsikan tabel order_items berisi kolom product_id seperti pada artikel sebelumnya.

IN cocok digunakan ketika hasil subquery dapat berisi beberapa nilai.

2.7. Subquery dengan EXISTS

Ada situasi ketika kita tidak membutuhkan daftar nilainya. Kita hanya ingin mengetahui apakah data terkait tersedia.

Misalnya, kita ingin menampilkan pelanggan yang memiliki setidaknya satu pesanan.

SELECT
    c.id,
    c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
);

Subquery memeriksa apakah terdapat pesanan untuk pelanggan yang sedang diperiksa.

Jika ada minimal satu baris yang cocok, EXISTS bernilai benar dan pelanggan ditampilkan.

Contoh hasil berdasarkan data TokoKita sebelumnya:

Dewi tidak muncul karena belum memiliki pesanan.

Sebaliknya, jika kita ingin mencari pelanggan yang tidak memiliki pesanan, kita dapat menggunakan NOT EXISTS.

SELECT
    c.id,
    c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
);

Hasilnya adalah Dewi.

EXISTS dan NOT EXISTS berguna untuk memeriksa keberadaan hubungan data. Untuk beberapa kebutuhan, pola ini juga menjadi alternatif bagi JOIN.

Referensi: MySQL 8.4 — Subqueries with EXISTS or NOT EXISTS.

2.8. Apa itu correlated subquery?

Subquery yang sudah kita pelajari sebelumnya dapat menghitung rata-rata harga seluruh produk tanpa bergantung pada baris produk tertentu.

Namun, ada jenis subquery yang merujuk pada kolom dari query bagian luar. Teknik ini disebut correlated subquery.

Sebagai contoh, kita ingin menampilkan produk yang harganya di atas rata-rata produk dalam kategori yang sama. Untuk latihan ini, anggap tabel products memiliki kolom category.

SELECT
    p.product_name,
    p.category,
    p.price
FROM products AS p
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM products AS p2
    WHERE p2.category = p.category
);

Perhatikan kondisi:

WHERE p2.category = p.category

Alias p2 merujuk pada tabel di dalam subquery, sedangkan p merujuk pada baris dari query utama.

Dengan demikian, perbandingan rata-rata dilakukan berdasarkan kategori produk yang sesuai, bukan rata-rata seluruh produk.

Subquery berkorelasi bisa sangat berguna, tetapi pada dataset besar sebaiknya periksa performanya menggunakan EXPLAIN dan bandingkan dengan pendekatan lain seperti agregasi terlebih dahulu lalu JOIN.

3. CASE WHEN: Membuat Kategori dan Logika Kondisi

Setelah memahami subquery, sekarang kita belajar CASE.

Bayangkan pemilik TokoKita tidak ingin membaca angka harga saja. Ia ingin laporan yang menampilkan label:

1. Murah.
2. Menengah.
3. Mahal.

Apakah kita perlu mengubah setiap harga menjadi kategori secara manual?

Tidak. SQL menyediakan ekspresi CASE untuk memilih hasil berdasarkan kondisi tertentu.

3.1. Sintaks dasar CASE

Bentuk umum yang paling sering digunakan adalah:

CASE
    WHEN kondisi_1 THEN hasil_1
    WHEN kondisi_2 THEN hasil_2
    ELSE hasil_lain
END

Artinya:

1. WHEN menentukan kondisi.
2. THEN menentukan hasil ketika kondisi terpenuhi.
3. ELSE menentukan hasil jika tidak ada kondisi sebelumnya yang terpenuhi.
4. END menutup ekspresi CASE.

MySQL memeriksa kondisi dalam urutan penulisan dan mengembalikan hasil dari kondisi pertama yang benar. Jika tidak ada kondisi yang cocok dan ELSE tidak ditulis, hasilnya adalah NULL.

Referensi: MySQL 8.4 — Flow Control Functions.

3.2. Mengelompokkan harga produk

Kita akan membuat aturan berikut:

1. Harga di bawah Rp200.000: Murah.
2. Harga dari Rp200.000 sampai Rp700.000: Menengah.
3. Harga di atas Rp700.000: Mahal.

Query-nya:

SELECT
    product_name,
    price,
    CASE
        WHEN price < 200000 THEN 'Murah'
        WHEN price <= 700000 THEN 'Menengah'
        ELSE 'Mahal'
    END AS price_category
FROM products
ORDER BY price DESC;

Hasilnya:

Mengapa kondisi kedua cukup menggunakan price <= 700000?

Karena baris tersebut baru diperiksa setelah kondisi pertama gagal. Jadi, nilai harga yang diperiksa pada kondisi kedua sudah tidak kurang dari Rp200.000.

Meskipun begitu, ketika menulis aturan bisnis yang kompleks, pastikan rentang nilai sudah jelas dan tidak menimbulkan celah atau tumpang tindih yang tidak diinginkan.

3.3. CASE untuk status transaksi

Selain kategori harga, CASE juga dapat digunakan untuk membuat label status transaksi.

Misalnya, status pesanan di database menggunakan nilai teknis seperti completed, pending, dan cancelled. Untuk laporan manajemen, kita ingin label yang lebih mudah dibaca.

SELECT
    id AS order_id,
    status,
    CASE
        WHEN status = 'completed' THEN 'Selesai'
        WHEN status = 'pending' THEN 'Menunggu'
        WHEN status = 'cancelled' THEN 'Dibatalkan'
        ELSE 'Status Lainnya'
    END AS status_label
FROM orders;

Query tersebut tidak mengubah nilai status asli di tabel. CASE hanya menghasilkan label tambahan dalam hasil query.

Hal ini berguna untuk laporan, dashboard, dan ekspor data.

3.4. CASE di dalam perhitungan agregasi

Kita juga bisa menggabungkan CASE dengan SUM() untuk membuat perhitungan bersyarat.

Contoh: menghitung jumlah pesanan yang sudah selesai dan jumlah pesanan yang masih menunggu.

SELECT
    COUNT(*) AS total_orders,
    SUM(
        CASE
            WHEN status = 'completed' THEN 1
            ELSE 0
        END
    ) AS completed_orders,
    SUM(
        CASE
            WHEN status = 'pending' THEN 1
            ELSE 0
        END
    ) AS pending_orders
FROM orders;

Logikanya cukup sederhana:

1. Jika status completed, hasil CASE adalah 1.
2. Jika bukan, hasilnya 0.
3. SUM() menjumlahkan angka tersebut.

Teknik ini sering digunakan untuk membuat ringkasan KPI dalam satu query.

3.5. CASE bukan untuk menyaring baris seperti WHERE

Perhatikan perbedaannya.

WHERE digunakan untuk menentukan baris mana yang masuk ke hasil query. Sementara itu, CASE digunakan untuk menghasilkan nilai berdasarkan kondisi.

Contoh menggunakan WHERE:

SELECT product_name, price
FROM products
WHERE price >= 200000;

Query tersebut hanya menampilkan produk dengan harga minimal Rp200.000.

Contoh menggunakan CASE:

SELECT
    product_name,
    price,
    CASE
        WHEN price >= 200000 THEN 'Memenuhi batas'
        ELSE 'Di bawah batas'
    END AS price_check
FROM products;

Query kedua tetap menampilkan seluruh produk, tetapi memberikan label sesuai harga masing-masing.

Pilih teknik berdasarkan kebutuhan: apakah ingin menyaring baris atau memberikan klasifikasi pada hasilnya.

4. UNION dan UNION ALL: Menggabungkan Hasil Query

Sampai di sini, kita sudah belajar menggabungkan tabel secara horizontal menggunakan JOIN.

Sekarang kita mengenal cara lain: menggabungkan hasil beberapa query secara vertikal menggunakan UNION.

Bayangkan TokoKita mempunyai dua sumber data pelanggan:

1. customers: pelanggan dari sistem utama.
2. legacy_customers: pelanggan lama dari aplikasi sebelumnya.

Tim ingin menggabungkan daftar nama pelanggan dari kedua sumber.

Jika struktur kolomnya sesuai, kita bisa menggunakan UNION.

4.1. Apa itu UNION?

UNION menggabungkan hasil dari dua atau lebih query SELECT menjadi satu result set. Secara default, UNION menghilangkan baris duplikat.

Contoh sederhana:

SELECT name AS customer_name
FROM customers

UNION

SELECT name AS customer_name
FROM legacy_customers;

Query pertama mengambil nama pelanggan dari tabel utama. Query kedua mengambil nama pelanggan dari tabel lama.

Hasil keduanya digabungkan menjadi satu kolom customer_name.

Catatan: legacy_customers merupakan tabel ilustrasi. Tabel tersebut harus tersedia di database sebelum query dapat dijalankan.

4.2. Syarat menggunakan UNION

Agar UNION dapat digunakan dengan benar, perhatikan beberapa aturan:

1. Setiap query harus menghasilkan jumlah kolom yang sama.
2. Kolom yang berada pada posisi yang sama harus memiliki tipe data yang kompatibel.
3. Nama kolom hasil gabungan umumnya mengikuti nama kolom dari query pertama.
4. Jika membutuhkan pengurutan hasil gabungan, letakkan ORDER BY pada bagian paling akhir.

Contoh yang benar:

SELECT id, name AS person_name
FROM customers

UNION

SELECT id, name AS person_name
FROM legacy_customers;

Kedua query menghasilkan dua kolom.

Contoh yang tidak sesuai:

SELECT id, name
FROM customers

UNION

SELECT id
FROM legacy_customers;

Query kedua hanya menghasilkan satu kolom, sedangkan query pertama menghasilkan dua kolom. Struktur tersebut tidak memenuhi syarat UNION.

4.3. Apa perbedaan UNION dan UNION ALL?

Inilah perbedaan penting yang harus dipahami.

Contoh menggunakan UNION ALL:

SELECT name AS customer_name
FROM customers

UNION ALL

SELECT name AS customer_name
FROM legacy_customers;

Pada contoh ini, jika nama yang sama muncul dari kedua tabel, baris tersebut tetap dapat muncul dua kali.

Perlu diingat, UNION menghilangkan baris yang duplikat pada keseluruhan kolom hasil, bukan hanya berdasarkan satu kolom yang kita anggap unik. Jika dua baris memiliki nama sama tetapi kolom lain berbeda, keduanya tidak otomatis dianggap duplikat.

4.4. Kapan menggunakan UNION ALL?

Misalnya, perusahaan mengumpulkan catatan transaksi dari beberapa sumber dan setiap baris merupakan peristiwa yang perlu dipertahankan.

Dalam kasus seperti itu, UNION ALL dapat lebih sesuai karena tidak menghapus baris duplikat. Selain itu, karena tidak perlu melakukan proses penghilangan duplikat, UNION ALL sering lebih efisien daripada UNION, meskipun performa tetap bergantung pada query dan kondisi data.

Sebaliknya, jika kita ingin menggabungkan daftar kota dari dua tabel dan hanya membutuhkan nilai unik, UNION mungkin lebih sesuai.

4.5. Menggabungkan data pelanggan dari dua sumber

Misalnya, kedua tabel pelanggan memiliki kolom name dan city. Kita ingin memperoleh daftar nama dan kota dari kedua sumber.

SELECT
    name AS person_name,
    city
FROM customers

UNION ALL

SELECT
    name AS person_name,
    city
FROM legacy_customers

ORDER BY person_name;

Perhatikan ORDER BY diletakkan setelah seluruh query gabungan. Dengan begitu, pengurutan berlaku pada hasil gabungan, bukan hanya pada satu sumber data.

4.6. UNION berbeda dengan JOIN

Jangan tertukar antara UNION dan JOIN.

JOIN menambahkan kolom dari tabel lain berdasarkan relasi. Contohnya, menggabungkan nama pelanggan dengan tanggal pesanan.

UNION menambahkan baris dari beberapa query dengan struktur kolom yang sesuai. Contohnya, menggabungkan daftar pelanggan dari dua sistem.

Cara mudah mengingatnya:

1. JOIN menggabungkan informasi antartabel secara horizontal.
2. UNION menggabungkan hasil query secara vertikal.

Referensi: MySQL 8.4 — UNION Clause.

5. CTE: Membuat Query Lebih Terstruktur dengan WITH

Sekarang kita sampai pada materi yang sangat berguna ketika query semakin panjang: Common Table Expression atau CTE.

Bayangkan pemilik TokoKita ingin melihat laporan total nilai penjualan setiap pesanan. Untuk setiap baris detail pesanan, nilai penjualan dihitung dari jumlah barang dikalikan harga produk.

Setelah itu, nilai detail dijumlahkan untuk memperoleh total setiap pesanan. Terakhir, laporan menampilkan pesanan dengan total nilai tertentu.

Kita bisa menyusun semuanya dalam satu query panjang. Namun, semakin banyak proses yang terlibat, semakin sulit query dibaca dan dipelihara.

CTE membantu memecah query menjadi bagian-bagian bernama yang lebih mudah dipahami.

5.1. Apa itu CTE?

CTE adalah result set sementara yang diberi nama dan hanya tersedia selama satu pernyataan SQL berlangsung.

CTE ditulis menggunakan WITH.

Bentuk umumnya:

WITH nama_cte AS (
    SELECT ...
)
SELECT ...
FROM nama_cte;

Perhatikan bahwa CTE bukan tabel permanen yang otomatis disimpan ke database. CTE merupakan bagian dari satu pernyataan SQL dan dapat dirujuk oleh query utama, serta oleh CTE lain yang didefinisikan setelahnya.

Referensi: MySQL 8.4 — WITH (Common Table Expressions).

5.2. Contoh CTE sederhana

Kita ingin membuat daftar produk yang harganya minimal Rp200.000.

WITH eligible_products AS (
    SELECT
        id,
        product_name,
        price
    FROM products
    WHERE price >= 200000
)
SELECT
    product_name,
    price
FROM eligible_products
ORDER BY price DESC;

Bagian WITH mendefinisikan CTE bernama eligible_products.

Query utama kemudian membaca hasil CTE tersebut seolah-olah sedang membaca sebuah tabel pada bagian FROM.

Hasilnya:

Kita bisa membaca query ini dalam dua tahap:

1. Ambil produk dengan harga minimal Rp200.000.
2. Tampilkan nama dan harga produk dari hasil tahap pertama.

5.3. Menggunakan lebih dari satu CTE

CTE juga bisa digunakan lebih dari satu dalam satu query. Setiap CTE dipisahkan dengan koma.

Sekarang kita ingin mengetahui total nilai penjualan untuk setiap pesanan. Kita akan menggunakan tabel orders, order_items, dan products dari seri sebelumnya.

WITH order_line_values AS (
    SELECT
        o.id AS order_id,
        o.order_date,
        o.status,
        oi.quantity * p.price AS line_total
    FROM orders AS o
    INNER JOIN order_items AS oi
        ON o.id = oi.order_id
    INNER JOIN products AS p
        ON oi.product_id = p.id
),
order_totals AS (
    SELECT
        order_id,
        order_date,
        status,
        SUM(line_total) AS total_amount
    FROM order_line_values
    GROUP BY
        order_id,
        order_date,
        status
)
SELECT
    order_id,
    order_date,
    status,
    total_amount
FROM order_totals
ORDER BY total_amount DESC;

Mari kita pahami prosesnya.

CTE pertama: order_line_values

Menggabungkan tabel pesanan, detail pesanan, dan produk. Setiap baris detail menghasilkan nilai quantity * price.

CTE kedua: order_totals

Mengelompokkan baris berdasarkan pesanan dan menjumlahkan nilai detail menggunakan SUM().

Query utama

Menampilkan total setiap pesanan dan mengurutkannya dari nilai terbesar.

Dengan cara ini, query memiliki tahapan yang jelas. Jika perhitungan nilai detail berubah, kita dapat memeriksa bagian CTE pertama tanpa harus membaca seluruh logika laporan sekaligus.

Catatan penting: contoh ini menghitung nilai menggunakan harga yang sekarang ada pada tabel products. Sistem transaksi yang menyimpan harga historis sebaiknya menggunakan harga satuan yang dicatat pada saat transaksi berlangsung.

5.4. CTE tidak otomatis membuat query lebih cepat

CTE terutama membantu keterbacaan dan pemeliharaan query. CTE tidak otomatis membuat query lebih cepat dibandingkan query biasa atau subquery.

MySQL dapat mengoptimalkan CTE dengan cara menggabungkan definisinya ke query utama (merge) atau mematerialisasi hasilnya sebagai result set internal. Strategi yang digunakan bergantung pada struktur query dan keputusan optimizer.

Karena itu, ketika query sudah digunakan pada data berukuran besar, periksa rencana eksekusinya dengan EXPLAIN dan uji performanya dengan data yang representatif.

5.5. Apa itu recursive CTE?

MySQL juga mendukung recursive CTE, yaitu CTE yang merujuk pada namanya sendiri untuk menghasilkan rangkaian data atau menelusuri hubungan hierarkis.

Contohnya, kita dapat membuat urutan angka sederhana:

WITH RECURSIVE numbers AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM numbers
    WHERE n < 5
)
SELECT n
FROM numbers;

Hasilnya:

Bagian awal menghasilkan angka 1. Bagian rekursif menambahkan 1 pada nilai sebelumnya selama nilainya masih kurang dari 5.

Kata RECURSIVE diperlukan untuk CTE yang merujuk pada dirinya sendiri. Kondisi penghentian seperti WHERE n < 5 penting agar proses rekursi tidak terus berjalan tanpa batas yang diinginkan.

Recursive CTE dapat digunakan untuk kebutuhan yang lebih lanjut, seperti struktur organisasi, kategori bertingkat, dan penelusuran hubungan antarentitas.

Untuk tahap pemula, cukup pahami konsep dasarnya terlebih dahulu. Kamu dapat mempelajari penerapan lanjutannya setelah menguasai CTE biasa.

6. Perbandingan Subquery, CASE, UNION, dan CTE

Keempat teknik ini memiliki fungsi yang berbeda. Tidak ada satu teknik yang selalu lebih baik untuk semua kebutuhan.

Dalam praktiknya, teknik-teknik ini bisa digunakan bersama. Misalnya, CTE dapat berisi agregasi penjualan, lalu query utama menggunakan CASE untuk memberi kategori pada hasilnya.

Hal yang paling penting bukan menghafal sintaks, melainkan memahami masalah yang ingin diselesaikan dan memilih pendekatan yang paling jelas.

7. Kesalahan Umum Saat Menggunakan Teknik SQL Lanjutan

Kesalahan 1: Subquery mengembalikan lebih dari satu nilai

Contoh:

SELECT product_name, price
FROM products
WHERE price = (
    SELECT price
    FROM products
    WHERE price > 200000
);

Jika subquery menghasilkan beberapa harga, operator = tidak dapat menerima banyak nilai dalam konteks scalar subquery tersebut.

Jika tujuannya mencocokkan dengan salah satu nilai dari daftar, gunakan IN:

SELECT product_name, price
FROM products
WHERE price IN (
    SELECT price
    FROM products
    WHERE price > 200000
);

Pilih operator berdasarkan bentuk hasil subquery yang diharapkan. Jika menggunakan =, pastikan subquery menghasilkan satu nilai yang sesuai.

Kesalahan 2: Lupa menulis ELSE pada CASE

Contoh:

CASE
    WHEN price < 200000 THEN 'Murah'
    WHEN price <= 700000 THEN 'Menengah'
END

Untuk harga di atas Rp700.000, hasilnya menjadi NULL karena tidak ada kondisi yang cocok dan tidak ada ELSE.

Jika semua baris harus memiliki label, tambahkan kondisi ELSE:

CASE
    WHEN price < 200000 THEN 'Murah'
    WHEN price <= 700000 THEN 'Menengah'
    ELSE 'Mahal'
END

Kesalahan 3: Menganggap UNION ALL menghilangkan duplikat

UNION ALL mempertahankan baris duplikat. Jika sumber data memiliki catatan yang sama, catatan tersebut tetap dapat muncul berkali-kali.

Pastikan kamu memahami apakah duplikat merupakan data yang sah atau merupakan duplikasi yang perlu ditangani.

Kesalahan 4: Jumlah kolom UNION tidak sama

Setiap query dalam UNION harus menghasilkan jumlah kolom yang sama. Periksa juga kesesuaian tipe data pada kolom yang dipasangkan.

Jangan menganggap dua query dapat digabungkan hanya karena berasal dari dua tabel yang berhubungan.

Kesalahan 5: Mengira CTE adalah tabel permanen

CTE hanya tersedia selama pernyataan SQL yang mendefinisikannya. Jika hasilnya harus disimpan untuk digunakan oleh query terpisah, kamu perlu mempertimbangkan tabel, temporary table, view, atau mekanisme penyimpanan lain yang sesuai.

Kesalahan 6: Menggunakan query kompleks tanpa memeriksa hasilnya

Ketika menggabungkan beberapa teknik sekaligus, pecah masalah menjadi langkah kecil.

Mulai dari query paling sederhana, periksa hasilnya, lalu tambahkan kondisi atau tahapan berikutnya. Pendekatan ini lebih mudah untuk menemukan kesalahan logika.

8. Latihan SQL: Uji Pemahamanmu

Gunakan tabel products, customers, orders, dan order_items yang sudah diperkenalkan dalam seri TokoKita.

Latihan 1 — Subquery

Tampilkan produk yang harganya di atas rata-rata harga seluruh produk.

Petunjuk: gunakan AVG(price) dalam subquery.

Jawaban:

SELECT
    product_name,
    price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Latihan 2 — CASE

Buat label harga dengan aturan:

1. Di bawah Rp200.000: Murah.
2. Sampai dengan Rp700.000: Menengah.
3. Di atas Rp700.000: Mahal.

Jawaban:

SELECT
    product_name,
    price,
    CASE
        WHEN price < 200000 THEN 'Murah'
        WHEN price <= 700000 THEN 'Menengah'
        ELSE 'Mahal'
    END AS price_category
FROM products;

Latihan 3 — UNION ALL

Gabungkan nama pelanggan dari tabel customers dan legacy_customers, dengan tetap mempertahankan duplikat.

Jawaban:

SELECT name AS person_name
FROM customers

UNION ALL

SELECT name AS person_name
FROM legacy_customers;

Pastikan tabel legacy_customers tersedia dan memiliki kolom name sebelum menjalankan query.

Latihan 4 — CTE

Buat CTE bernama expensive_products yang mengambil produk dengan harga minimal Rp200.000, lalu tampilkan nama produk dan harga.

Jawaban:

WITH expensive_products AS (
    SELECT
        product_name,
        price
    FROM products
    WHERE price >= 200000
)
SELECT
    product_name,
    price
FROM expensive_products;

Latihan 5 — Tantangan gabungan

Gunakan CTE untuk menghitung total nilai detail setiap pesanan, kemudian gunakan CASE untuk mengategorikan pesanan:

1. Di bawah Rp500.000: Kecil.
2. Rp500.000 sampai Rp1.000.000: Menengah.
3. Di atas Rp1.000.000: Besar.

Jawaban:

WITH order_line_values AS (
    SELECT
        o.id AS order_id,
        oi.quantity * p.price AS line_total
    FROM orders AS o
    INNER JOIN order_items AS oi
        ON o.id = oi.order_id
    INNER JOIN products AS p
        ON oi.product_id = p.id
),
order_totals AS (
    SELECT
        order_id,
        SUM(line_total) AS total_amount
    FROM order_line_values
    GROUP BY order_id
)
SELECT
    order_id,
    total_amount,
    CASE
        WHEN total_amount < 500000 THEN 'Kecil'
        WHEN total_amount <= 1000000 THEN 'Menengah'
        ELSE 'Besar'
    END AS order_category
FROM order_totals
ORDER BY total_amount DESC;

Perhatikan bahwa query tersebut mengasumsikan harga pada tabel products digunakan untuk menghitung nilai detail pesanan. Pada sistem produksi, gunakan harga historis transaksi jika tersedia.

9. Bagaimana Materi Ini Membantu Karier di Bidang Data dan Teknologi?

Menguasai SQL bukan sekadar mampu menulis perintah SELECT. Kamu juga perlu memahami bagaimana menyusun logika untuk menjawab pertanyaan bisnis.

Untuk Data Analyst, subquery dapat digunakan untuk membandingkan nilai, CASE membantu membuat kategori dan KPI, sedangkan UNION membantu menyatukan data dari beberapa sumber.

Untuk Data Scientist, teknik-teknik tersebut membantu menyiapkan dataset sebelum proses eksplorasi dan pemodelan. Kualitas dataset sangat berpengaruh terhadap hasil analisis.

Untuk Data Engineer, pemahaman CTE dan subquery membantu menyusun transformasi data yang lebih terstruktur. Query dapat menjadi bagian dari pipeline pemrosesan data, dengan tetap memperhatikan performa dan kebutuhan operasional.

Untuk Software Engineer, kemampuan SQL lanjutan membantu membangun laporan, pencarian, dan logika pengambilan data yang dibutuhkan aplikasi.

Tidak harus langsung menguasai semua materi sekaligus. Kuasai satu konsep, praktikkan pada contoh sederhana, lalu gabungkan dengan konsep yang sudah dipelajari sebelumnya.

10. Ringkasan Artikel

Pada artikel kelima ini, kita telah mempelajari:

1. Subquery untuk menggunakan hasil query lain sebagai bagian dari query utama.
2. IN, EXISTS, dan NOT EXISTS untuk mencocokkan nilai atau memeriksa keberadaan data.
3. CASE WHEN untuk membuat kategori, label, dan perhitungan bersyarat.
4. UNION dan UNION ALL untuk menggabungkan hasil beberapa query.
5. CTE dengan WITH untuk menyusun query menjadi beberapa tahapan yang lebih mudah dibaca.
6. Recursive CTE sebagai pengantar untuk membuat rangkaian data atau menelusuri hubungan hierarkis.

Jika artikel sebelumnya mengajarkan cara mengambil data, mengubah data, menghitung agregasi, dan menggabungkan tabel, sekarang kamu sudah mulai memahami cara membangun logika query yang lebih fleksibel.

11. Artikel Selanjutnya: SQL untuk Data Analytics

Setelah menguasai dasar-dasar SQL hingga CTE, kita akan melanjutkan ke materi yang lebih dekat dengan kebutuhan analisis bisnis.

Pada artikel keenam, kita akan membahas SQL untuk Data Analytics: Fungsi Tanggal, Window Functions, dan KPI.

Kita akan mempelajari cara membuat laporan bulanan, membandingkan performa penjualan, menghitung peringkat produk, membuat running total, dan menganalisis tren menggunakan query SQL.

Jika kamu ingin mengulang materi sebelumnya, lanjutkan kembali dari:

1. Artikel 01 — Belajar SQL untuk Pemula: Database, Tabel, dan SELECT.
2. Artikel 02 — Belajar SQL CRUD: INSERT, UPDATE, dan DELETE.
3. Artikel 03 — Fungsi Agregasi SQL: COUNT, SUM, AVG, GROUP BY, dan HAVING.
4. Artikel 04 — SQL JOIN: INNER JOIN, LEFT JOIN, dan Relasi Tabel.

Kesimpulan: Subquery, CASE, UNION, dan CTE membantu kita menulis query yang bukan hanya mengambil data, tetapi juga membangun logika analisis. Dengan memahami kapan dan bagaimana menggunakan masing-masing teknik, kamu akan lebih siap menyelesaikan kebutuhan laporan bisnis dan pengolahan data menggunakan SQL.

Referensi Resmi

1. MySQL 8.4 — Subqueries
2. MySQL 8.4 — Flow Control Functions (CASE)
3. MySQL 8.4 — UNION Clause
4. MySQL 8.4 — WITH (Common Table Expressions)
5. MySQL 8.4 — Optimizing Subqueries, Derived Tables, and CTEs