Join yang tidak jebol

Join mencocokkan baris dari dua tabel dan mengembalikan kombinasinya. Hanya itu yang dia lakukan — dan hampir semua bug join berasal dari lupa separuh kedua kalimat itu: kombinasi. Kalau satu baris di kiri cocok dengan tiga baris di kanan, kamu dapat tiga baris keluaran.

INNER JOIN

SELECT c.full_name, o.id, o.status
FROM customer c
JOIN "order" o ON o.customer_id = c.id;

JOIN berarti INNER JOIN. Baris bertahan hanya ketika kondisinya benar di kedua sisi — customer tanpa order hilang, dan order dengan customer_id menggantung juga hilang. Di skema kita foreign key membuat kasus kedua mustahil, dan itulah gunanya dia ada.

LEFT JOIN, dan jebakannya

LEFT JOIN menyimpan setiap baris dari tabel kiri, mengisi sisi kanan dengan NULL ketika tidak ada yang cocok.

SELECT c.full_name, o.id
FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id;

Sekarang inilah kesalahannya, dan ini bug join paling umum yang ada:

-- SALAH: ini INNER JOIN yang memakai kostum LEFT JOIN
SELECT c.full_name, o.id
FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id
WHERE o.status = 'paid';

LEFT JOIN-nya patuh menghasilkan baris NULL untuk customer yang tidak punya order berbayar — lalu WHERE o.status = 'paid' bernilai NULL untuk baris-baris itu dan membuangnya. Kamu meminta semua customer dan hanya dapat yang punya order berbayar.

Perbaikannya adalah memutuskan kondisi itu untuk apa:

-- Filter pada tabel kanan tempatnya di ON: dia menentukan apa yang dianggap cocok
SELECT c.full_name, o.id
FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id AND o.status = 'paid';

-- Filter pada tabel kiri tempatnya di WHERE: dia menentukan baris mana yang ditimbang
SELECT c.full_name, o.id
FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id
WHERE c.country = 'ID';

Untuk INNER JOIN perbedaan ini tidak mengubah hasil, dan justru itu sebabnya orang terbiasa menaruh semuanya di WHERE lalu tergigit begitu pertama kali mengganti jenis join-nya.

RIGHT JOIN

RIGHT JOIN adalah LEFT JOIN dengan tabel yang ditukar. Dia ada, kedua mesin mendukungnya, dan jarang dipakai karena satu alasan: pembacanya harus memegang dua tabel dalam urutan terbalik dari cara penulisannya. Tulis LEFT JOIN dan tukar urutan tabelnya.

FULL OUTER JOIN

Menyimpan baris tak-cocok dari kedua sisi.

Di MySQL kamu menyusunnya dari dua outer join dan sebuah UNION:

-- PostgreSQL
SELECT c.id, o.id
FROM customer c
FULL OUTER JOIN "order" o ON o.customer_id = c.id;

-- MySQL: hasil yang sama
SELECT c.id AS customer_id, o.id AS order_id
FROM customer c LEFT JOIN `order` o ON o.customer_id = c.id
UNION
SELECT c.id, o.id
FROM customer c RIGHT JOIN `order` o ON o.customer_id = c.id;

Pakai UNION, bukan UNION ALL — baris yang cocok muncul di kedua paruh dan UNION membuang duplikatnya. Pembuangan duplikat itu juga sebabnya ini lebih lambat daripada yang aslinya.

CROSS JOIN, yang disengaja dan yang tidak

Cross join mengembalikan setiap kombinasi: 1.000 customer dikali 500 produk adalah 500.000 baris.

-- Disengaja, dan sah: kisi untuk di-left-join dengan data sungguhan
SELECT c.id, p.id FROM customer c CROSS JOIN product p;

Versi tak sengajanya datang dari sintaks koma lama dengan kondisi yang terlupa:

-- Dua tabel, tanpa kondisi join. Ini cross join.
SELECT * FROM customer c, "order" o;

Tidak ada yang memperingatkanmu. Di tabel kecil dia tampak seperti query lambat; di tabel besar dia mengisi disk. Ini argumen terkuat untuk selalu menulis JOIN ... ON secara eksplisit: ON yang hilang adalah error sintaks, sementara WHERE yang hilang cuma hari Selasa.

Semi-join dan anti-join

Seringkali kamu tidak butuh kolom dari tabel sebelah — hanya ingin tahu apakah ada yang cocok. Men-join untuk alasan itu menggandakan baris lalu butuh DISTINCT untuk membereskan kerusakannya.

-- Semi-join: customer yang punya setidaknya satu order. Tanpa duplikat, tanpa DISTINCT.
SELECT * FROM customer c
WHERE EXISTS (SELECT 1 FROM "order" o WHERE o.customer_id = c.id);

-- Anti-join: customer yang tidak punya
SELECT * FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM "order" o WHERE o.customer_id = c.id);

-- Anti-join, ejaan lain: LEFT JOIN lalu simpan yang tidak cocok
SELECT c.* FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id
WHERE o.id IS NULL;

Ketiganya sah. EXISTS menyatakan maksudnya, dan dia tidak pernah punya masalah NULL NOT IN dari materi 2. Kedua planner biasanya mengeksekusi bentuk EXISTS dan LEFT JOIN secara identik.

Self join

Tabel yang di-join ke dirinya sendiri butuh alias, karena tanpa itu tidak ada rujukan kolom yang tidak ambigu.

-- Pasangan order dari customer yang sama dalam selang satu jam
SELECT a.id, b.id, a.customer_id
FROM "order" a
JOIN "order" b
  ON b.customer_id = a.customer_id
 AND b.id > a.id                                  -- setiap pasangan sekali, bukan dua kali
 AND b.placed_at < a.placed_at + interval '1 hour';

b.id > a.id mengerjakan hal nyata: tanpa dia setiap pasangan muncul dua kali, satu per arah, ditambah setiap baris yang di-join ke dirinya sendiri. Di MySQL intervalnya a.placed_at + INTERVAL 1 HOUR.

USING, dan kenapa NATURAL JOIN itu jebakan

-- ON, ditulis lengkap
JOIN order_item i ON i.order_id = o.id

-- USING: hanya ketika nama kolomnya sama di kedua tabel
SELECT * FROM "order" o JOIN order_item i USING (order_id);   -- kalau o punya order_id

USING juga menyatukan kolomnya sehingga muncul sekali di SELECT *, yang sesekali memang yang kamu mau.

NATURAL JOIN men-join berdasarkan setiap kolom yang namanya sama, tanpa kamu menyebutkannya. Kedua mesin mendukungnya, dan sebaiknya dihindari: tambahkan kolom created_at di kedua tabel dan join itu diam-diam mulai mencocokkannya juga, mengubah hasil query yang tidak disentuh siapa pun.

Urutan join, dan apa yang dilakukan planner terhadapnya

Urutan penulisan join tidak menentukan urutan eksekusinya. Kedua mesin menyusun ulang join berdasarkan statistik ukuran tabel dan sebaran nilai.

EXPLAIN
SELECT c.full_name, i.quantity
FROM customer c
JOIN "order" o      ON o.customer_id = c.id
JOIN order_item i   ON i.order_id = o.id
WHERE c.country = 'ID';

Keduanya bisa mulai dari customer (kalau country = 'ID' selektif) atau dari order_item (kalau tidak). Materi 8 membahas cara membaca keputusan itu.

Sesekali planner-nya salah dan kamu perlu memaksa:

Keduanya jalan terakhir. Sebuah hint adalah statistik yang dibekukan pada saat kamu menulisnya, dan dia berhenti benar begitu datanya bertambah.

Banyak join, dan fan-out lagi

Setiap join satu-ke-banyak menggandakan baris, dan beberapa join semacam itu saling menggandakan:

Bayangkan ada tabel shipment di samping empat tabel kita — satu order bisa punya beberapa, sama seperti dia punya beberapa item:

-- Kalau satu order punya 3 item dan 2 pengiriman, ini mengembalikan 6 baris per order
SELECT o.id, i.product_id, s.tracking_code
FROM "order" o
JOIN order_item i ON i.order_id = o.id
JOIN shipment   s ON s.order_id = o.id;

Tidak ada yang salah secara sintaks di sini, dan agregat apa pun di atasnya adalah omong kosong. Kalau kamu butuh dua relasi satu-ke-banyak yang independen dalam satu hasil, agregasi masing-masing dulu secara terpisah — seperti di materi 3 — atau pakai dua query.

Yang perlu dibawa pulang

• Filter pada tabel yang di-outer-join tempatnya di ON. Di WHERE dia membatalkan outer join-nya.
• MySQL tidak punya FULL OUTER JOIN; susun dengan LEFT + RIGHT + UNION.
• JOIN ... ON eksplisit, jangan join koma — ON yang hilang gagal dengan berisik.
• Pakai EXISTS / NOT EXISTS kalau kamu hanya butuh keberadaan.
• Hindari NATURAL JOIN; kolom baru diam-diam mengubah join-nya.
• Dua join satu-ke-banyak dalam satu query saling menggandakan. Agregasi sebelum join.