Belajar JOIN Table dari Gambar Legendaris

Ilustrasi SQL JOINS paling legendaris di Sosial Media

Ilustrasi SQL JOINS ini adalah salah satu gambar yang populer di berbagai media sosial sebagai alat bantu visual untuk memahami konsep SQL JOINS. Namun, berapa banyak yang benar-benar memahami maksud dari ilustrasi tersebut dengan baik? Banyak yang justru merasa bingung dan mengernyitkan dahi saat melihatnya, bahkan ada yang merasa paham, padahal masih kebingungan.

Oleh karena itu, saya membuat artikel ini dengan ilustrasi yang mudah dipahami, menggunakan contoh tabel users dan cars. Dalam contoh ini, kita akan menemui situasi di mana ada baris pada tabel users yang memiliki cars, ada yang tidak, dan sebaliknya pada tabel cars—ada car yang dimiliki oleh user, dan ada juga yang sama sekali tidak memiliki pemilik.

Kondisinya akan seperti berikut:

1. Ada pengguna yang memiliki satu mobil.
2. Ada pengguna yang memiliki beberapa mobil.
3. Ada pengguna yang tidak memiliki mobil.
4. Ada mobil yang tidak memiliki pemilik.

Melalui situasi di atas, kita akan belajar berbagai jenis SQL JOINS dengan cara yang mudah dipahami, menggunakan studi kasus sederhana yang mendekati situasi nyata.

Buat Skema Database dan dua Table diatas

Untuk MySQL, berikut adalah skrip SQL yang memenuhi persyaratan yang diberikan. Dalam skrip ini, kita membuat dua tabel (users dan cars) dalam skema belajar, dengan hubungan one-to-many yang memungkinkan sebuah user memiliki banyak car, tetapi car atau user dapat berdiri sendiri tanpa berhubungan satu sama lain.

-- Create schema if it doesn't exist

CREATE SCHEMA IF NOT EXISTS belajar;

USE belajar;

-- Create the 'users' table

CREATE TABLE IF NOT EXISTS users (

user_id INT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL UNIQUE,

email VARCHAR(100) NOT NULL UNIQUE,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

-- Create the 'cars' table

CREATE TABLE IF NOT EXISTS cars (

car_id INT AUTO_INCREMENT PRIMARY KEY,

user_id INT NULL, -- optional relation to users

make VARCHAR(50) NOT NULL,

model VARCHAR(50) NOT NULL,

year YEAR CHECK (year > 1885), -- assume first car was created in 1886

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE SET NULL

);

Hasil eksekusi skrip DDL SQL diatas

Hasil eksekusi skrip DDL SQL diatas

Insert Data untuk di Query

-- Insert 10 rows into users table

INSERT INTO belajar.users (username, email) VALUES

('user1', 'user1@example.com'), -- Multiple cars

('user2', 'user2@example.com'), -- Multiple cars

('user3', 'user3@example.com'), -- Multiple cars

('user4', 'user4@example.com'), -- Single car

('user5', 'user5@example.com'), -- Single car

('user6', 'user6@example.com'), -- Single car

('user7', 'user7@example.com'), -- No car

('user8', 'user8@example.com'), -- No car

('user9', 'user9@example.com'), -- No car

('user10', 'user10@example.com'); -- No car

-- Insert 15 rows into cars table

INSERT INTO belajar.cars (user_id, make, model, year) VALUES

(1, 'Toyota', 'Camry', 2020), -- user1

(1, 'Honda', 'Accord', 2019), -- user1

(1, 'Ford', 'Focus', 2021), -- user1

(2, 'Nissan', 'Altima', 2018), -- user2

(2, 'Chevrolet', 'Malibu', 2022), -- user2

(2, 'Hyundai', 'Elantra', 2017), -- user2

(3, 'BMW', 'X5', 2020), -- user3

(3, 'Mercedes', 'C-Class', 2019), -- user3

(3, 'Audi', 'A4', 2018), -- user3

(4, 'Mazda', 'CX-5', 2021), -- user4

(5, 'Volkswagen', 'Passat', 2020),-- user5

(6, 'Subaru', 'Impreza', 2019), -- user6

(NULL, 'Tesla', 'Model 3', 2022),

(NULL, 'Ford', 'Mustang', 2021),

(NULL, 'Chevrolet', 'Corvette', 2020),

(NULL, 'Dodge', 'Charger', 2019),

(NULL, 'Toyota', 'Supra', 2021);

Isi table users

Isi Table Cars

Selanjutnya mari kita bahas satu per satu

1. Select dengan Left Join

left join table (users di kiri cars di kanan)

Dalam kasus ini, LEFT JOIN digunakan untuk memastikan bahwa kita mendapatkan semua data users, baik yang memiliki mobil maupun yang tidak. Jika menggunakan INNER JOIN, kita hanya mendapatkan pengguna yang memiliki mobil saja, sehingga kita kehilangan data pengguna yang belum memiliki kendaraan.

Query LEFT JOIN yang Sesuai

SELECT

u.user_id,

u.username,

u.email,

c.car_id,

c.make,

c.model,

c.year

FROM

belajar.users u

LEFT JOIN

belajar.cars c ON u.user_id = c.user_id;

Penjelasan Hasil

• Pengguna seperti user1 muncul beberapa kali karena memiliki beberapa mobil.
• Pengguna seperti user7 dan user10 muncul meskipun tidak memiliki mobil, dengan data kolom mobil (car_id, make, model, year) diisi NULL.

Contoh kasus lain, bila user yang ingin ditampilkan hanya yang memiliki mobil > 1

users dengan jumlah mobil > 1

2. Select dengan Left Join Yang Tak Punya Mobil Saja

left join table (users di kiri cars di kanan), tidak punya mobil. 

SELECT

u.user_id,

u.username,

u.email,

c.car_id,

c.make,

c.model,

c.year

FROM

belajar.users u

LEFT JOIN

belajar.cars c ON u.user_id = c.user_id

WHERE

c.car_id IS NULL;

Penjelasan Query

LEFT JOIN digunakan untuk menyertakan semua data dari tabel users, tetapi hanya menampilkan baris di mana c.car_id bernilai NULL (bila c.user_id is null juga akan sama saja), artinya pengguna tersebut tidak memiliki mobil.

3. Select dengan Left Join Tapi Yang Punya Mobil Saja

Sama dengan no dua cuma perbedaannya adalah pada c.car_id nya harus NOT NULL.

SELECT

u.user_id,

u.username,

u.email,

c.car_id,

c.make,

c.model,

c.year

FROM

belajar.users u

LEFT JOIN

belajar.cars c ON u.user_id = c.user_id

WHERE

c.car_id IS NOT NULL;

4. Select dengan Inner Join

Inner Join

Penggunaan INNER JOIN sangat berguna ketika kita hanya memerlukan data dari dua tabel yang memiliki keterkaitan langsung, artinya hanya menampilkan data yang memiliki kecocokan di kedua tabel. 

SELECT

u.user_id,

u.username,

u.email,

c.car_id,

c.make,

c.model,

c.year

FROM

belajar.users u

INNER JOIN

belajar.cars c ON u.user_id = c.user_id;

Inner Join

Hasil dari inner join diatas sama dengan left join berikut.

SELECT

u.user_id,

u.username,

u.email,

c.car_id,

c.make,

c.model,

c.year

FROM

belajar.users u

LEFT JOIN

belajar.cars c ON u.user_id = c.user_id

WHERE

c.car_id IS NOT NULL;

Left Join

Berikut adalah perbandingan antara INNER JOIN dan LEFT JOIN dengan kondisi WHERE c.car_id IS NOT NULL dalam bentuk tabel:

Aspek	INNER JOIN	LEFT JOIN + WHERE
Query	sql	sql
	SELECT	SELECT
	u.user_id,	u.user_id,
	u.username,	u.username,
	u.email,	u.email,
	c.car_id,	c.car_id,
	c.make,	c.make,
	c.model,	c.model,
	c.year	c.year
	FROM	FROM
	belajar.users u	belajar.users u
	INNER JOIN	LEFT JOIN
	belajar.cars c ON u.user_id = c.user_id;	belajar.cars c ON u.user_id = c.user_id
		WHERE
		c.car_id IS NOT NULL;
Hasil	Hanya pengguna yang memiliki mobil.	Hanya pengguna yang memiliki mobil.
		(Hasil akhir sama dengan INNER JOIN, tetapi prosesnya berbeda.)
Data dari Users	Hanya pengguna dengan entri di tabel Cars.	Semua pengguna, tetapi hanya pengguna dengan mobil yang ditampilkan.
Data dari Cars	Data terkait kendaraan yang dimiliki pengguna.	Data terkait kendaraan yang dimiliki pengguna (NULL untuk yang tidak memiliki kendaraan tidak ditampilkan).
Kinerja	Lebih efisien karena hanya mengambil data yang relevan.	Kurang efisien karena mengambil semua pengguna terlebih dahulu sebelum menyaring.
Kejelasan	Lebih jelas dan langsung menunjukkan tujuan query.	Bisa membingungkan karena mengimplikasikan pengambilan data semua pengguna.

Kesimpulan

• INNER JOIN adalah pilihan yang lebih efisien dan jelas ketika kita hanya ingin data yang memiliki kecocokan di kedua tabel.
• LEFT JOIN dengan kondisi WHERE IS NOT NULL menghasilkan hasil yang sama, tetapi dengan proses yang kurang efisien dan lebih membingungkan dalam konteks tujuannya.

4. Select dengan Full Outer Join

FULL OUTER JOIN sangat berguna dalam situasi audit, pelaporan data yang tidak lengkap, atau ketika kita ingin menganalisis semua data dari dua tabel, baik yang memiliki kecocokan maupun yang tidak. Hal ini membantu dalam memastikan tidak ada data yang terlewat, yang sangat penting dalam pelaporan bisnis, manajemen inventaris, serta analisis pelanggan atau karyawan.

Mari kita terapkan konsep FULL OUTER JOIN pada contoh tabel users dan cars. Dalam skenario ini, kita mungkin ingin mendapatkan daftar lengkap dari semua pengguna dan semua mobil, terlepas dari apakah setiap pengguna memiliki mobil atau apakah setiap mobil memiliki pemilik.

Kasus Nyata dengan FULL OUTER JOIN

Bayangkan kita adalah perusahaan penyewaan atau leasing kendaraan. Kita ingin meninjau data pengguna yang tidak memiliki mobil (mungkin target potensial untuk ditawarkan paket sewa) sekaligus melihat mobil yang tidak memiliki pemilik (mungkin kendaraan yang belum disewa atau diklaim). Untuk ini, FULL OUTER JOIN membantu kita melihat:

• Pengguna yang memiliki mobil.
• Pengguna yang tidak memiliki mobil.
• Mobil yang dimiliki pengguna.
• Mobil yang tidak dimiliki oleh pengguna mana pun.

Query Full Outer Join (Tidak di support MySQL)

SELECT

u.user_id,

u.username,

u.email,

c.car_id,

c.make,

c.model,

c.year

FROM

belajar.users u

FULL OUTER JOIN

belajar.cars c ON u.user_id = c.user_id;

Simulasi Query FULL OUTER JOIN

Karena MySQL tidak mendukung FULL OUTER JOIN langsung, kita bisa mensimulasinya dengan menggabungkan LEFT JOIN dan RIGHT JOIN menggunakan UNION:

SELECT

u.user_id,

u.username,

u.email,

c.car_id,

c.make,

c.model,

c.year

FROM

belajar.users u

LEFT JOIN

belajar.cars c ON u.user_id = c.user_id

UNION

SELECT

u.user_id,

u.username,

u.email,

c.car_id,

c.make,

c.model,

c.year

FROM

belajar.users u

RIGHT JOIN

belajar.cars c ON u.user_id = c.user_id;

Penjelasan Query

1. Bagian LEFT JOIN: Mengambil semua data dari tabel users dan menggabungkannya dengan data dari tabel cars, dengan NULL di kolom cars untuk pengguna yang tidak memiliki mobil.
2. Bagian RIGHT JOIN: Mengambil semua data dari tabel cars dan menggabungkannya dengan tabel users, dengan NULL di kolom users untuk mobil yang tidak memiliki pemilik.
3. UNION: Menggabungkan hasil dari kedua join, memberikan hasil yang mirip dengan FULL OUTER JOIN.

Hasil Query Full Outer Join dengan Simulasi Union (MySQL tidak support outer join)

Contoh lain OUTER JOIN untuk mendapatkan users yang tidak punya mobil dan mobilyang tidak memiliki pemilik.

SELECT

u.user_id,

u.username,

u.email,

c.car_id,

c.make,

c.model,

c.year

FROM

belajar.users u

LEFT JOIN

belajar.cars c ON u.user_id = c.user_id

WHERE

c.car_id IS NULL OR u.user_id IS NULL

UNION

SELECT

u.user_id,

u.username,

u.email,

c.car_id,

c.make,

c.model,

c.year

FROM

belajar.users u

RIGHT JOIN

belajar.cars c ON u.user_id = c.user_id

WHERE

c.car_id IS NULL OR u.user_id IS NULL;

Users tanpa mobil atau mobil tanpa users.