Penggabungan Tabel (2) — OUTER JOIN, CROSS JOIN, Self-Join

OUTER JOIN — mempertahankan baris yang hanya ada di satu sisi

INNER JOIN dari sesi sebelumnya hanya mengembalikan baris yang cocok di kedua tabel, sehingga karyawan tanpa departemen dan departemen tanpa karyawan hilang dari hasil. Ketika kebutuhannya adalah "saya ingin semua karyawan; orang tanpa departemen boleh memiliki nama departemen kosong", kamu memakai OUTER JOIN yang selalu mempertahankan baris di salah satu sisi.

Ada 3 jenis OUTER JOIN tergantung sisi mana yang kamu pertahankan: LEFT JOIN mempertahankan setiap baris tabel kiri, RIGHT JOIN mempertahankan setiap baris tabel kanan, dan FULL OUTER JOIN mempertahankan setiap baris dari keduanya. Kolom dari sisi yang tidak cocok diisi dengan NULL. Konsol kursus ini memakai SQLite, dan kamu bisa menjalankan LEFT / RIGHT / FULL apa adanya. Datanya sama seperti sesi sebelumnya: department (6 departemen) dan employee (30 karyawan).

LEFT JOIN — selalu mempertahankan tabel kiri

Ketika kamu menulis SELECT cols FROM left LEFT JOIN right ON condition, setiap baris tabel kiri dipertahankan, dan jika tidak ada baris yang cocok di tabel kanan maka kolom sisi kanan menjadi NULL. LEFT JOIN adalah singkatan dari LEFT OUTER JOIN, dan keduanya berperilaku sama. Kamu memakainya ketika ingin mendaftar sisi utama tanpa menghilangkan siapa pun, seperti "tampilkan semua karyawan, dan biarkan nama departemen kosong untuk orang tanpa departemen".

-- Pertahankan semua karyawan. Jika tidak ada departemen, dept_name adalah NULL
SELECT e.name, e.city, d.dept_name
FROM employee e
LEFT JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

Bayangkan kebutuhan: "saya ingin menemukan karyawan yang tidak termasuk ke departemen mana pun, menyejajarkan dept_id dari sisi karyawan dan sisi departemen, dan memastikan kenapa nama departemen tidak bisa dicari". (Jalankan dengan benar dan penjelasannya akan muncul.)

① LEFT JOIN employee (alias e) di kiri dan department (alias d) di kanan, lalu persempit hanya ke baris yang d.dept_id-nya NULL.

② Selain e.name dan e.city, ambil `e.dept_id` (sisi karyawan) dan `d.dept_id` / `d.dept_name` (sisi departemen). Pastikan secara visual bahwa karena e.dept_id itu sendiri NULL (tidak ada departemen yang ditetapkan) tidak ada pasangan join, sehingga d.dept_id dan d.dept_name juga NULL. Urutkan berdasarkan e.name menaik.

③ Lalu ubah join yang sama menjadi INNER JOIN dan pastikan bahwa tidak ada satu baris pun yang kembali (karena baris yang kunci join-nya NULL hilang pada INNER JOIN).

RIGHT JOIN dan FULL OUTER JOIN

RIGHT JOIN adalah LEFT JOIN dengan kiri dan kanan ditukar — ia mempertahankan setiap baris tabel kanan. Ketika kamu menulis employee e RIGHT JOIN department d ON ..., departemen Legal yang tanpa karyawan sama sekali tetap ada di hasil, dan kolom sisi employee menjadi NULL. Ini cocok untuk "tampilkan semua departemen, dan tampilkan departemen tanpa karyawan dengan kolom kosong".

FULL OUTER JOIN mempertahankan setiap baris kedua tabel. Baris yang cocok digabung berdampingan, baris yang hanya ada di kiri (4 orang tanpa departemen) menjadi NULL di sisi kanan, dan baris yang hanya ada di kanan (departemen Legal) menjadi NULL di sisi kiri — semuanya tersusun dalam satu hasil.

-- RIGHT JOIN: pertahankan semua departemen. Legal NULL di sisi karyawan
SELECT d.dept_name, e.name
FROM employee e
RIGHT JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

-- FULL OUTER JOIN: pertahankan baik karyawan tanpa departemen maupun departemen tanpa karyawan
SELECT e.name, d.dept_name
FROM employee e
FULL OUTER JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

Bayangkan kebutuhan: "saya ingin memastikan departemen yang tidak ada satu karyawan pun ditetapkan, dengan menyejajarkan kolom sisi departemen dan kolom sisi karyawan".

① RIGHT JOIN employee (alias e) di kiri dan department (alias d) di kanan, lalu persempit hanya ke baris yang e.emp_id-nya NULL.

② Ambil d.dept_id / d.dept_name / d.location (sisi departemen) dan `e.emp_id` / `e.name` (sisi karyawan). Pastikan bahwa d.dept_id memiliki nilai (nomor departemen) sementara e.emp_id dan e.name NULL (kosong) — yang berarti departemen itu benar-benar ada tetapi memiliki 0 karyawan yang ditetapkan. Urutkan berdasarkan d.dept_name menaik.

Bayangkan kebutuhan: "saya ingin mencocokkan karyawan dan departemen ke dalam satu tabel, menyejajarkan dept_id di kedua sisi, dan memastikan bagaimana baris yang hanya ada di satu sisi menjadi NULL".

① FULL OUTER JOIN employee (alias e) dan department (alias d). Kondisi join adalah kecocokan dept_id.

② Ambil e.emp_id / e.name / `e.dept_id` (sisi karyawan) dan `d.dept_id` / `d.dept_name` (sisi departemen). Baris yang cocok memiliki nilai di kedua sisi, tetapi karyawan tanpa departemen yang ditetapkan memiliki baik `e.dept_id` maupun `d.dept_id` NULL, dan departemen tanpa karyawan (Legal) memiliki nilai di `d.dept_id` dan NULL di sisi `e`. Pastikan secara visual setiap "baris satu sisi saja" termasuk pola yang mana.

③ Urutkan berdasarkan d.dept_id menaik (baris yang d.dept_id-nya NULL mengelompok di bagian atas).

CROSS JOIN — membangun setiap kombinasi

CROSS JOIN tidak memiliki kondisi join dan membangun setiap kombinasi dari tiap baris tabel kiri dengan tiap baris tabel kanan. Jumlah baris hasil adalah jumlah baris kiri × jumlah baris kanan (hasil kali Kartesius). Untuk employee (30 baris) dan department (6 baris), itu 30 × 6 = 180 baris.

Dalam praktik kamu memakainya untuk mendaftar setiap kombinasi yang mungkin seperti "semua karyawan × semua bulan" atau "semua toko × semua produk", lalu menggabungkan data aktual ke atasnya dan mengisi sel tanpa data aktual dengan 0 — sebagai fondasi sebuah agregasi. Karena ini join tanpa kondisi, kamu tidak menambahkan ON.

-- Hitung jumlah kombinasi semua-karyawan x semua-departemen
SELECT count(*) AS combo_count
FROM employee e
CROSS JOIN department d;

-- Periksa sebagian kombinasi (hanya pasangan dengan departemen Sales)
SELECT e.name, d.dept_name
FROM employee e
CROSS JOIN department d
WHERE d.dept_name = 'Sales'
ORDER BY e.emp_id;

Bayangkan kebutuhan: "saya ingin menghitung ada berapa kombinasi yang mungkin antara semua karyawan dan semua departemen".

① CROSS JOIN employee (alias e) dan department (alias d) (jangan menulis kondisi join).

② Hitung jumlah total kombinasi dengan count(*), dan beri alias kolom hasil sebagai combo_count.

Self-join — memakai tabel yang sama dua kali untuk mencari nama manajer

Tabel employee memiliki kolom manager_id (nomor karyawan manajer), dan manajer juga adalah seorang karyawan di tabel employee yang sama. Ketika kamu ingin "menampilkan nama tiap karyawan berdampingan dengan nama manajernya", kamu JOIN tabel yang sama dua kali. Ini disebut self-join.

Kamu menulisnya sebagai FROM employee e JOIN employee m ON e.manager_id = m.emp_id. Kamu membuat tabel yang sama muncul dua kali dengan alias yang memisahkan peran — e sebagai "sisi karyawan" dan m sebagai "sisi manajer". Alias wajib dalam self-join; tanpanya kamu tidak bisa membedakan employee mana yang mana. Dengan INNER JOIN, karyawan tanpa manajer hilang, jadi pakai LEFT JOIN jika kamu ingin mendaftar semua karyawan.

-- Tampilkan nama manajer tiap karyawan berdampingan (hanya karyawan yang punya manajer: INNER)
SELECT e.name AS employee, m.name AS manager
FROM employee e
JOIN employee m
  ON e.manager_id = m.emp_id
ORDER BY e.emp_id;

Bayangkan kebutuhan: "saya ingin mendaftar semua karyawan dengan nama manajernya berdampingan. Karyawan tanpa manajer boleh memiliki nama manajer kosong".

① LEFT JOIN employee sebagai alias e (sisi karyawan) dengan employee lagi sebagai alias m (sisi manajer). Kondisi join adalah e.manager_id = m.emp_id.

② Ambil 2 kolom, beri alias e.name sebagai employee dan m.name sebagai manager.

③ Urutkan berdasarkan e.emp_id menaik.

NULL dari OUTER JOIN berubah makna tergantung di mana kamu meletakkan WHERE

Jika setelah LEFT JOIN kamu menulis kondisi pada kolom sisi kanan seperti WHERE right_table.column = value, NULL selalu dievaluasi sebagai salah dalam perbandingan, jadi baris tanpa pasangan jatuh dan kamu pada dasarnya mendapat hasil yang sama dengan INNER JOIN. Ketika kamu ingin mempersempit sambil mempertahankan "baris tanpa pasangan", tulis kondisi itu di sisi ON, atau pakai WHERE right_table.key IS NULL untuk secara eksplisit mengambil "baris tanpa pasangan". Untuk pemeriksaan NULL, selalu pakai IS NULL / IS NOT NULL, jangan pernah = NULL.