KAL
Menu

FULL OUTER dan CROSS JOIN

Mengenal join tanpa pasangan di kedua sisi dan produk kartesian beserta kegunaannya.

Daftar isi

Topik ini membahas dua join yang paling jarang dipakai sekaligus paling sering salah paham, yaitu FULL OUTER JOIN yang menjaga baris tanpa pasangan dari kedua tabel, dan CROSS JOIN yang menghasilkan seluruh kombinasi. Keduanya bukan alat sehari-hari, tetapi masing-masing menjadi solusi baku untuk satu kelas masalah.

Blok penyiapan dataset#

bash 
# dataset kecil perencanaan bulanan untuk topik ini
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS realisasi, rencana, ukuran, warna CASCADE;
CREATE TABLE rencana (bulan TEXT PRIMARY KEY, target INTEGER NOT NULL);
CREATE TABLE realisasi (bulan TEXT PRIMARY KEY, tercapai INTEGER NOT NULL);
INSERT INTO rencana VALUES ('Mei', 120), ('Juni', 150), ('Juli', 130);
INSERT INTO realisasi VALUES ('Juni', 141), ('Juli', 128), ('Agustus', 137);
CREATE TABLE ukuran (nama TEXT PRIMARY KEY);
CREATE TABLE warna (nama TEXT PRIMARY KEY);
INSERT INTO ukuran VALUES ('S'), ('M'), ('L');
INSERT INTO warna VALUES ('Merah'), ('Biru');
SQL

Dataset perencanaan sengaja asimetris, yaitu Mei hanya direncanakan, Agustus hanya direalisasikan, sedangkan Juni dan Juli berpasangan. Dataset kedua berisi ukuran dan warna untuk latihan kombinasi.

FULL OUTER JOIN menjaga kedua sisi#

FULL OUTER JOIN mempertahankan baris yang berpasangan, baris kiri yang tanpa pasangan, dan baris kanan yang tanpa pasangan, sehingga laporan penuh tidak kehilangan dua sisi sekaligus.

bash 
# perbandingan rencana dan realisasi yang saling melengkapi
psql -U postgres -d perpustakaan -c "SELECT r.bulan AS rencana_bulan, r.target, s.bulan AS realisasi_bulan, s.tercapai FROM rencana r FULL OUTER JOIN realisasi s ON r.bulan = s.bulan ORDER BY COALESCE(r.bulan, s.bulan);"
text Keluaran 
 rencana_bulan | target | realisasi_bulan | tercapai
---------------+--------+-----------------+----------
               |        | Agustus         |      137
 Juli          |    130 | Juli            |      128
 Juni          |    150 | Juni            |      141
 Mei           |    120 |                 |
(4 rows)

Mei muncul dengan kolom realisasi kosong, Agustus muncul dengan kolom rencana kosong, dan Juni serta Juli berpasangan lengkap. Pengurutan memakai COALESCE atas kedua kolom bulan, karena baris tanpa pasangan hanya membawa satu sisi bulan, dan fungsi tersebut dibahas pada topik semantik NULL.

Kasus pemakaian yang lazim adalah rekonsiliasi dua sumber, misalnya data yang direncanakan dan data yang tercatat, karena kelalaian di sisi mana pun tetap terlihat. Apabila hanya satu sisi yang boleh kehilangan pasangan, LEFT JOIN tetap menjadi pilihan yang lebih jelas maksudnya.

CROSS JOIN dan produk kartesian#

CROSS JOIN menggabungkan setiap baris kiri dengan setiap baris kanan tanpa syarat, sehingga tiga ukuran dan dua warna menghasilkan enam kombinasi. Bentuk ini sah untuk membangun kerangka kombinasi yang akan diisi angka nol, pola yang disebut pengisian rangka.

bash 
# enam kombinasi ukuran dan warna
psql -U postgres -d perpustakaan -c "SELECT u.nama AS ukuran, w.nama AS warna FROM ukuran u CROSS JOIN warna w ORDER BY u.nama, w.nama;"
text Keluaran 
 ukuran | warna
--------+-------
 L      | Biru
 L      | Merah
 M      | Biru
 M      | Merah
 S      | Biru
 S      | Merah
(6 rows)

Pengisian rangka bekerja dengan menggabungkan kerangka kombinasi ini ke data yang ada melalui LEFT JOIN, sehingga kombinasi tanpa penjualan tetap tampil dengan angka nol alih-alih hilang dari laporan. Tanpa kerangka tersebut, laporan penjualan per kombinasi hanya memuat kombinasi yang pernah terjual, dan kekosongan yang justru penting itu tidak terlihat.

Kombinasi tersembunyi dan skalanya#

Produk kartesian tumbuh perkalian, sehingga seribu baris yang dikali seribu baris menghasilkan satu juta baris, dan seratus ribu dikali seratus ribu melampaui sepuluh miliar. Biayanya muncul di memori dan waktu, dan CROSS JOIN yang tidak disengaja biasanya ditemukan dari jumlah baris keluaran yang tak masuk akal.

Bentuk koma pada FROM, yaitu FROM a, b, setara CROSS JOIN dan menjadi warisan sintaks lama yang masih dikenali. Query modern menyatakan maksudnya eksplisit, sehingga CROSS JOIN ditulis apa adanya ketika kombinasi memang diinginkan, dan join bersyarat ditulis dengan ON.

Kesalahan umum#

Kesalahan pertama adalah memakai CROSS JOIN sebagai penyaring, padahal syaratnya lupa dituliskan dan seluruh kombinasi ikut keluar, kejadian yang biasanya ditemukan melalui jumlah baris yang melonjak. Kesalahan kedua adalah memakai FULL OUTER JOIN demi aman, padahal makna baris tanpa pasangan pada dua sisi itu tidak selalu jelas dan laporannya jadi sulit dibaca.

Kesalahan ketiga adalah mengurutkan hasil FULL OUTER JOIN menurut kolom dari satu sisi saja, sehingga baris yang hanya ada di sisi lain jatuh ke posisi yang tak terduga, dan pengurutan atas COALESCE kedua kolom menjadi pola yang aman.

Ringkasan#

  • FULL OUTER JOIN menjaga baris tanpa pasangan dari kedua sisi, dan pasangannya dibaca melalui COALESCE kolom kunci.
  • Penggunaan utamanya adalah rekonsiliasi dua sumber yang kelalaian di sisi mana pun harus terlihat.
  • CROSS JOIN menghasilkan seluruh kombinasi dan menjadi kerangka pengisian rangka pada laporan per kombinasi.
  • Ukuran keluarannya tumbuh perkalian, sehingga kombinasi besar diperhitungkan sebelum dijalankan.

Referensi#