KAL
Menu

Penyegaran SQL dengan kasus tepi

Penyegaran SQL melalui kasus tepi yang sering menjebak: perilaku NULL, jebakan join, dan agregasi melampaui hal dasar.

Daftar isi

Topik ini menyegarkan SQL dari sisi yang paling sering menjebak pekerjaan data engineering, yaitu perilaku NULL, jebakan join, dan agregasi melampaui bentuk dasarnya. Seluruh contoh dijalankan dengan Python 3.12 dan DuckDB 2.0 melalui skrip kecil yang membangun basis data contoh di direktori kerja. Setiap skrip menampilkan keluaran aslinya, sehingga perilaku yang dijelaskan dapat diverifikasi ulang kapan pun. Setelah membaca, query harian dapat ditulis dengan lebih waspada terhadap baris yang tidak terlihat.

Dataset contoh#

Dataset yang dipakai di sepanjang topik ini adalah catatan penjualan kecil sebuah kedai kopi yang tersimpan dalam dua tabel. Tabel pelanggan menyimpan anggota dengan kolom id, nama, dan kota, sedangkan tabel pesanan menyimpan id, pelanggan_id, tanggal, dan total. Nilai NULL muncul secara alami di dua tempat, yaitu kota pelanggan yang belum tercatat dan pelanggan_id pada pembelian tanpa keanggotaan. Skrip berikut membangun ulang basis data kopi.duckdb dari nol setiap kali dijalankan, sehingga isi datanya selalu sama.

python siapkan_basisdata.py 
import os
import duckdb

# basis data kecil dibangun ulang setiap kali skrip dijalankan
if os.path.exists("kopi.duckdb"):
    os.remove("kopi.duckdb")

con = duckdb.connect("kopi.duckdb")
con.execute("""
CREATE TABLE pelanggan (
    id    INTEGER PRIMARY KEY,
    nama  VARCHAR NOT NULL,
    kota  VARCHAR
)
""")
con.execute("""
INSERT INTO pelanggan VALUES
    (1, 'Ali',   'Bandung'),
    (2, 'Budi',  'Jakarta'),
    (3, 'Citra', 'Bandung'),
    (4, 'Dewi',  NULL),
    (5, 'Eka',   'Surabaya')
""")
con.execute("""
CREATE TABLE pesanan (
    id           INTEGER PRIMARY KEY,
    pelanggan_id INTEGER,
    tanggal      DATE,
    total        DECIMAL(10, 2)
)
""")
con.execute("""
INSERT INTO pesanan VALUES
    (101, 1,    DATE '2024-05-01', 25000),
    (102, 1,    DATE '2024-05-03', 18000),
    (103, 2,    DATE '2024-05-03', 32000),
    (104, NULL, DATE '2024-05-04', 15000),
    (105, 3,    DATE '2024-05-05', 27000),
    (106, NULL, DATE '2024-05-06', 22000),
    (107, 4,    DATE '2024-05-07', 19000),
    (108, 5,    DATE '2024-05-09', 41000)
""")

for tabel in ("pelanggan", "pesanan"):
    jumlah = con.execute(f"SELECT COUNT(*) FROM {tabel}").fetchone()[0]
    print(f"{tabel}: {jumlah} baris")
con.close()
text Keluaran 
pelanggan: 5 baris
pesanan: 8 baris

Dua angka pada keluaran menjadi patokan bagi seluruh topik, yaitu lima baris pelanggan dan delapan baris pesanan. Pada tabel pesanan, dua baris tidak memiliki pelanggan_id dan satu baris merujuk pelanggan yang kotanya tidak tercatat. Ketiga baris itulah yang akan sering muncul dan hilang pada contoh-contoh berikutnya.

Perilaku NULL pada perbandingan#

NULL bukanlah nilai, melainkan penanda bahwa nilai itu tidak ada, sehingga setiap perbandingan yang menyentuh NULL menghasilkan NULL dan bukan benar atau salah. Konsekuensinya, baris dengan kota NULL tidak pernah lolos pada penyaringan kota = 'Bandung', dan tidak lolos pula pada penyaringan kebalikannya. Kaidah ini disebut logika tiga nilai (three-valued logic), yakni logika yang mengenal benar, salah, dan tidak diketahui. Skrip berikut menunjukkan empat perilaku yang paling sering mengagetkan secara berurutan.

python perilaku_null.py 
import duckdb


def tampilkan(con, sql):
    """Cetak hasil query sebagai tabel rapi berkolom sejajar."""
    hasil = con.execute(sql)
    kolom = [d[0] for d in hasil.description]
    baris = hasil.fetchall()
    lebar = []
    for i in range(len(kolom)):
        anggota = [len(kolom[i])] + [len(str(b[i])) for b in baris]
        lebar.append(max(anggota))
    fmt = "  ".join(f"{{:<{w}}}" for w in lebar)
    print(fmt.format(*kolom).rstrip())
    for b in baris:
        print(fmt.format(*["NULL" if v is None else str(v) for v in b]).rstrip())


con = duckdb.connect("kopi.duckdb")

print("1) WHERE kota = 'Bandung': baris dengan kota NULL tidak ikut")
tampilkan(con, "SELECT id, nama, kota FROM pelanggan WHERE kota = 'Bandung'")

print()
print("2) WHERE kota <> 'Bandung': NULL juga tidak lolos")
jumlah = con.execute("SELECT COUNT(*) FROM pelanggan WHERE kota <> 'Bandung'").fetchone()[0]
print(f"   baris yang lolos: {jumlah}")

print()
print("3) IS NULL menangkap baris yang tadi hilang")
tampilkan(con, "SELECT id, nama FROM pelanggan WHERE kota IS NULL")

print()
print("4) COALESCE mengganti NULL dengan label yang mudah dibaca")
tampilkan(con, "SELECT id, nama, COALESCE(kota, 'tidak tercatat') AS kota FROM pelanggan")
con.close()
text Keluaran 
1) WHERE kota = 'Bandung': baris dengan kota NULL tidak ikut
id  nama   kota
1   Ali    Bandung
3   Citra  Bandung

2) WHERE kota <> 'Bandung': NULL juga tidak lolos
   baris yang lolos: 2

3) IS NULL menangkap baris yang tadi hilang
id  nama
4   Dewi

4) COALESCE mengganti NULL dengan label yang mudah dibaca
id  nama   kota
1   Ali    Bandung
2   Budi   Jakarta
3   Citra  Bandung
4   Dewi   tidak tercatat
5   Eka    Surabaya

Pada percobaan pertama, hanya Ali dan Citra yang muncul, sedangkan Dewi yang kotanya NULL menghilang tanpa pesan apa pun. Percobaan kedua memperlihatkan kasus yang lebih sering terlewat, yaitu pencarian kebalikannya juga kehilangan Dewi, sehingga baris yang lolos hanya dua dari lima. Percobaan ketiga memakai IS NULL, satu-satunya cara untuk menguji ketiadaan nilai, dan Dewi kembali ditemukan. Percobaan keempat memakai fungsi COALESCE yang mengembalikan argumen pertama yang bukan NULL, sehingga laporan dapat menampilkan label baca alih-alih sel kosong.

Perbedaan COUNT(*) dan COUNT(kolom)#

COUNT(*) menghitung seluruh baris hasil, sedangkan COUNT(kolom) hanya menghitung baris yang kolomnya tidak bernilai NULL. Perbedaan ini membuat kedua bentuk COUNT memberi jawaban yang berbeda pada tabel yang sama. Skrip berikut menghitung keduanya sekaligus pada tabel pesanan, ditambah COUNT(DISTINCT ...) yang hanya menghitung nilai unik.

python count_vs_count.py 
import duckdb


def tampilkan(con, sql):
    """Cetak hasil query sebagai tabel rapi berkolom sejajar."""
    hasil = con.execute(sql)
    kolom = [d[0] for d in hasil.description]
    baris = hasil.fetchall()
    lebar = []
    for i in range(len(kolom)):
        anggota = [len(kolom[i])] + [len(str(b[i])) for b in baris]
        lebar.append(max(anggota))
    fmt = "  ".join(f"{{:<{w}}}" for w in lebar)
    print(fmt.format(*kolom).rstrip())
    for b in baris:
        print(fmt.format(*["NULL" if v is None else str(v) for v in b]).rstrip())


con = duckdb.connect("kopi.duckdb")

tampilkan(con, """
SELECT COUNT(*)                       AS baris_total,
       COUNT(pelanggan_id)            AS baris_beranggota,
       COUNT(DISTINCT pelanggan_id)   AS anggota_unik,
       SUM(total)                     AS omzet
FROM pesanan
""")
con.close()
text Keluaran 
baris_total  baris_beranggota  anggota_unik  omzet
8            6                 5             199000.00

Kolom baris_total menunjukkan 8, kolom baris_beranggota menunjukkan 6, dan selisih keduanya persis dua pesanan tanpa keanggotaan. Kolom anggota_unik menunjukkan 5 karena Ali melakukan dua pesanan, sehingga COUNT(DISTINCT pelanggan_id) menghitungnya sekali. Fungsi agregat lain mengikuti kaidah yang sama, yaitu SUM, AVG, MIN, dan MAX melewati baris NULL, sehingga kolom omzet tetap terisi dari baris yang punya nilai. Membandingkan COUNT(*) dengan COUNT(kolom) juga merupakan pengukur kelengkapan kolom yang murah sebelum analisis lain dijalankan.

Jebakan LEFT JOIN dengan filter pada tabel kanan#

Menaruh syarat pada kolom tabel kanan di dalam WHERE mengubah LEFT JOIN menjadi INNER JOIN secara efektif. Penyebabnya sama dengan bagian sebelumnya, yaitu baris tanpa pasangan membawa NULL pada semua kolom tabel kanan, lalu syarat yang melibatkan NULL tidak pernah bernilai benar. Skrip berikut membandingkan tiga bentuk query yang sekilas terlihat setara.

python jebakan_join.py 
import duckdb


def tampilkan(con, sql):
    """Cetak hasil query sebagai tabel rapi berkolom sejajar."""
    hasil = con.execute(sql)
    kolom = [d[0] for d in hasil.description]
    baris = hasil.fetchall()
    lebar = []
    for i in range(len(kolom)):
        anggota = [len(kolom[i])] + [len(str(b[i])) for b in baris]
        lebar.append(max(anggota))
    fmt = "  ".join(f"{{:<{w}}}" for w in lebar)
    print(fmt.format(*kolom).rstrip())
    for b in baris:
        print(fmt.format(*["NULL" if v is None else str(v) for v in b]).rstrip())


con = duckdb.connect("kopi.duckdb")

print("a) LEFT JOIN tanpa filter tabel kanan: 8 pesanan tetap muncul")
tampilkan(con, """
SELECT s.id, s.pelanggan_id, p.kota, s.total
FROM pesanan s
LEFT JOIN pelanggan p ON p.id = s.pelanggan_id
""")
print()

print("b) LEFT JOIN plus WHERE kota = 'Bandung': sisanya terbuang")
tampilkan(con, """
SELECT s.id, s.pelanggan_id, p.kota, s.total
FROM pesanan s
LEFT JOIN pelanggan p ON p.id = s.pelanggan_id
WHERE p.kota = 'Bandung'
""")
print()

print("c) Perbaikannya: syarat kota dipindah ke ON")
tampilkan(con, """
SELECT s.id, s.pelanggan_id, p.kota, s.total
FROM pesanan s
LEFT JOIN pelanggan p
       ON p.id = s.pelanggan_id
      AND p.kota = 'Bandung'
""")
con.close()
text Keluaran 
a) LEFT JOIN tanpa filter tabel kanan: 8 pesanan tetap muncul
id   pelanggan_id  kota      total
101  1             Bandung   25000.00
102  1             Bandung   18000.00
103  2             Jakarta   32000.00
104  NULL          NULL      15000.00
105  3             Bandung   27000.00
106  NULL          NULL      22000.00
107  4             NULL      19000.00
108  5             Surabaya  41000.00

b) LEFT JOIN plus WHERE kota = 'Bandung': sisanya terbuang
id   pelanggan_id  kota     total
101  1             Bandung  25000.00
102  1             Bandung  18000.00
105  3             Bandung  27000.00

c) Perbaikannya: syarat kota dipindah ke ON
id   pelanggan_id  kota     total
101  1             Bandung  25000.00
102  1             Bandung  18000.00
103  2             NULL     32000.00
104  NULL          NULL     15000.00
105  3             Bandung  27000.00
106  NULL          NULL     22000.00
107  4             NULL     19000.00
108  5             NULL     41000.00

Percobaan pertama mengembalikan seluruh delapan pesanan, lengkap dengan dua pesanan tanpa anggota yang kolom kotanya NULL. Percobaan kedua memindahkan keinginan menampilkan pesanan pelanggan Bandung ke dalam WHERE, sehingga hasilnya tinggal tiga baris. Sisanya terbuang diam-diam, yaitu dua pesanan tanpa anggota, pesanan dari kota lain, dan pesanan milik Dewi. Perilaku ini sering lolos dari pemeriksaan karena tidak ada pesan error dan angkanya masih terlihat masuk akal. Percobaan ketiga memperlihatkan perbaikannya, yaitu syarat kota dipindahkan ke dalam ON sehingga penyaringan terjadi sebelum baris tanpa pasangan dilengkapi NULL, dan seluruh delapan pesanan kembali muncul.

GROUP BY, HAVING, dan kelompok NULL#

Agregasi memadatkan banyak baris menjadi satu baris per kelompok, dan semua baris yang kolom pengelompokannya NULL masuk ke satu kelompok yang sama. Aturan kedua yang sering keliru adalah letak penyaringannya, yaitu WHERE menyaring baris sebelum agregasi, sedangkan HAVING menyaring kelompok sesudah agregasi. Skrip berikut menggabungkan pesanan dengan pelanggan, lalu menghitung omzet per kota dalam tiga percobaan, dan dua percobaan pertama diurutkan dengan ORDER BY omzet DESC agar urutan keluarannya tetap.

python agregasi_having.py 
import duckdb


def tampilkan(con, sql):
    """Cetak hasil query sebagai tabel rapi berkolom sejajar."""
    hasil = con.execute(sql)
    kolom = [d[0] for d in hasil.description]
    baris = hasil.fetchall()
    lebar = []
    for i in range(len(kolom)):
        anggota = [len(kolom[i])] + [len(str(b[i])) for b in baris]
        lebar.append(max(anggota))
    fmt = "  ".join(f"{{:<{w}}}" for w in lebar)
    print(fmt.format(*kolom).rstrip())
    for b in baris:
        print(fmt.format(*["NULL" if v is None else str(v) for v in b]).rstrip())


con = duckdb.connect("kopi.duckdb")

print("1) GROUP BY membentuk satu kelompok dari semua kota NULL")
tampilkan(con, """
SELECT p.kota, COUNT(*) AS jumlah_pesanan, SUM(s.total) AS omzet
FROM pesanan s
LEFT JOIN pelanggan p ON p.id = s.pelanggan_id
GROUP BY p.kota
ORDER BY omzet DESC
""")
print()

print("2) HAVING menyaring hasil agregasi per kelompok")
tampilkan(con, """
SELECT p.kota, COUNT(*) AS jumlah_pesanan, SUM(s.total) AS omzet
FROM pesanan s
LEFT JOIN pelanggan p ON p.id = s.pelanggan_id
GROUP BY p.kota
HAVING SUM(s.total) > 40000
ORDER BY omzet DESC
""")
print()

print("3) WHERE yang memuat fungsi agregat ditolak oleh mesin SQL")
try:
    con.execute("SELECT pelanggan_id, SUM(total) AS omzet FROM pesanan WHERE SUM(total) > 40000 GROUP BY pelanggan_id")
except Exception as e:
    print(f"{type(e).__name__}: {e}")
con.close()
text Keluaran 
1) GROUP BY membentuk satu kelompok dari semua kota NULL
kota      jumlah_pesanan  omzet
Bandung   3               70000.00
NULL      3               56000.00
Surabaya  1               41000.00
Jakarta   1               32000.00

2) HAVING menyaring hasil agregasi per kelompok
kota      jumlah_pesanan  omzet
Bandung   3               70000.00
NULL      3               56000.00
Surabaya  1               41000.00

3) WHERE yang memuat fungsi agregat ditolak oleh mesin SQL
BinderException: Binder Error: WHERE clause cannot contain aggregates!

LINE 1: ...ELECT pelanggan_id, SUM(total) AS omzet FROM pesanan WHERE SUM(total) > 40000 GROUP BY pelanggan_id
                                                                      ^^^^^^^^^^

Percobaan pertama menghasilkan kelompok NULL dengan tiga pesanan dan omzet 56000.00, yaitu gabungan dua pesanan tanpa anggota dan satu pesanan milik Dewi. Kelompok ini sah menurut standar SQL dan tidak menghilang seperti pada penyaringan baris, sehingga laporan per kota harus memutuskan secara sadar apakah kelompok tanpa kota ditampilkan, dilabeli dengan COALESCE, atau disaring dengan WHERE kota IS NOT NULL. Percobaan kedua menambahkan HAVING SUM(s.total) > 40000, sehingga hanya Bandung, kelompok NULL, dan Surabaya yang lolos. Percobaan ketiga menunjukkan sisi lain dari aturan letaknya, yaitu WHERE yang memuat SUM langsung ditolak binder, komponen mesin SQL yang memeriksa keabsahan query, dengan pesan error yang jelas.

Kesalahan umum#

Empat kekeliruan berikut paling sering muncul pada pekerjaan SQL harian, beserta penyebab dan perbaikannya.

  • Membandingkan kolom dengan NULL menggunakan tanda sama dengan. Perbandingan itu tidak pernah benar karena hasilnya NULL, sehingga baris yang dicari tidak muncul. Gunakan IS NULL atau IS NOT NULL.
  • Menyaring kolom tabel kanan di dalam WHERE sesudah LEFT JOIN. Syarat itu membuang baris tanpa pasangan dan mengubah join menjadi INNER JOIN secara efektif. Pindahkan syaratnya ke dalam ON.
  • Menghitung nilai terisi dengan COUNT(*). Bentuk ini menghitung seluruh baris termasuk yang NULL, sehingga angkanya terlalu besar. Gunakan COUNT(kolom) atau COUNT(DISTINCT kolom) sesuai maksud.
  • Menyaring hasil agregasi dengan WHERE. Fungsi agregat di dalam WHERE ditolak binder saat query diperiksa. Gunakan HAVING untuk menyaring kelompok hasil agregasi.

Ringkasan#

  • NULL tidak pernah lolos perbandingan biasa, baik pada pencarian maupun pengecualiannya, sehingga IS NULL dan COALESCE wajib disiapkan setiap kali kolom boleh NULL.
  • COUNT(*) menghitung semua baris, COUNT(kolom) menghitung baris terisi, dan selisih keduanya mengukur kelengkapan kolom.
  • Syarat pada kolom tabel kanan di dalam WHERE mengubah LEFT JOIN menjadi INNER JOIN secara efektif, sedangkan penempatannya di dalam ON mempertahankan baris kiri.
  • WHERE menyaring baris sebelum agregasi dan tidak boleh memuat fungsi agregat, sedangkan HAVING menyaring kelompok sesudahnya, dan baris NULL membentuk satu kelompok tersendiri.

Referensi#