Belajar SQL Join

Mungkin sudah banyak yang melihat gambar legendaris ini baik di social media atau di website-website tapi belum paham maksudnya. Gambar ini adalah visualisasi dari berbagai macam contoh SQL Join yang sering digunakan sehari-hari dalam dunia pemrograman dan basis data. Nah saat ini untuk mudahnya saya buat contoh kasusnya.



Nah pada postingan ini saya mencoba memberikan contoh paling sederhana dengan menbuat contoh join antara table-table berikut: users, cars dan motorcycles.

Kasusnya kurang lebih nanti akan seperti ini:

1. Munculkan users dan mobilnya.
2. Munculkan users dan mobilnya, yang tidak punya mobil jangan dimunculkan.
3. Munculkan mobil yang ada pemiliknya, yang tidak ada pemiliknya jangan di tampilkan.
4. Dan lain-lain.

Untuk awal mari persiapkan table-table yang diperlukan yaitu table users, table cars dan motorcycles.

Buat Dulu Table users

create table users (
  id serial primary key, -- Auto incrementing IDs
  username character varying, -- String column without specifying a length
  job character varying -- String column without specifying a length
);

Ada 7 orang dengan 4 profesi berbeda.

Isi dari table users:

PNS

1. Joni
1. Mobil Kia Merah
2. Motor Honda Hitam
2. Deni
1. Motor Kawasaki Biru

TNI

1. Ismed
1. Mobil Suzuki Putih
2. Mobil Mitsubishi Hitam
2. Ruslan
1. Motor Jialing Putih

Polri

1. Sufyan 
1. Mobil Hyundai Putih
2. Motor Honda Putih
2. Suryadi
1. Motor Yamaha Merah

Wiraswasta

1. Kurniawan
1. Mobil Toyota Kuning
2. Motor Viar Hijau
3. Motor Harley Ungu

Total ada 5 mobil dan 7 motor yang kebetulan di miliki oleh beberapa users dari beragam propesi.

Buat Tables cars dan motorcycles

create table cars (
  id serial primary key, -- Auto incrementing IDs
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  brand character varying, -- String column without specifying a length
  color character varying -- String column without specifying a length
);

Cars

create table motorcycles (
  id serial primary key, -- Auto incrementing IDs
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  brand character varying, -- String column without specifying a length
  color character varying -- String column without specifying a length
);

Motorcycles

Buat Isi Table

Jangan lupa tambahkan Motor BSA dan Mobil Koenigsegg yang tidak dimiliki Siapapun

Yang kiri mobil yang kanan motor.

Nah sampai disini kita sudah memiliki tiga buat table, yaitu: users, cars dan motorcycles. Ketiga table ini dimaksudnya untuk menggambarkan hubungan antara barang (cars dan motorcycles) dan pemiliknya (users), ada user yang punya mobil tak punya motor ada user punya motor dan tak punya mobil dll.

Contoh Inner Join (Ini default join)

users adalah A (left) dan cars adalah B (right).

Hanya munculkan data saat users punya mobil dan  cars ada yang punya.
Ingat users disini ada di left dan cars ada di right dan operasi adalah inner join (ini paling sering dipakai).

select 

users.id as user_id,

users.username,

users.job,

cars.brand AS car_brand

from users inner join cars on users.id = cars.user_id;

Bawah ini sama inner join juga.

Nah, dari operasi inner join ini maka hanya users yang punya mobil yang tampil dan juga cars yang ada pemilik yang tampil, bandingkan dengan left join di bawah, akan ada users yang tak punya mobil ikut muncul dan kolom mobil dihasil joinnya null.

Contoh Left Join

Lihat left adalah user dan kanan adalah cars, karena ini LEFT JOIN, maka semua di LEFT akan di join walau dia tidak punya data di sebelah RIGHT. Maksudnya pada kasus ini akan ada users yang muncul tapi kolom-kolom cars nya null.

users adalah A (left) dan cars adalah B (right).

Munculkan semua users baik dia punya mobil atau tidak punya mobil.

select 

users.id as user_id,

users.username,

users.job,

cars.brand AS car_brand

from users LEFT JOIN cars on users.id = cars.user_id;

LIhat semua di LEFT (users) ikut JOIN walaupun tidak punya data di RIGHT (cars)

Contoh Right Join

users adalah A (left) dan cars adalah B (right).

Nah yang ini adalah kebalikan dari LEFT JOIN, ini akan menampikan semua mobil bahkan yang tidak ada pemiliknya, jadi akan ada table dengan data cars tapi usersnya null.

select 

users.id as user_id,

users.username,

users.job,

cars.brand AS car_brand

from users right join cars on users.id = cars.user_id;

Full Outer Join (Anggap Left, Inner dan Right Join Digabung)

users adalah A (left) dan cars adalah B (right).

Ambil semuanya yang di kiri dan kanan, sehingga dalam kasus ini akan ada row yang memiliki data cars tapi usersnya null dan sebaliknya juga akan ada data users tapi carsnya null (akan ada users tanpa memiliki mobil dan mobil tanpa pemilik).

Studi Kasus Dunia Nyata

Studi kasus sederhana di bawah ini adalah contoh penerapan SQL join diatas dalam situasi sebenarnya, kasus-kasus tersebut bisa jadi berubah kasus seperti dibawah ini:

Tampilkan users yang memiliki mobil (untuk motor sama saja).

select
 users.id as user_id,
 users.username,
 users.job,
 cars.brand as car_brand
from users LEFT JOIN cars on users.id = cars.user_id

WHERE cars.brand IS NOT NULL;

Tampilkan user yang tidak memiliki mobil (untuk motor sama saja).

select
 users.id as user_id,
 users.username,
 users.job,
 cars.brand as car_brand
from users LEFT JOIN cars on users.id = cars.user_id

WHERE cars.brand IS NULL;

Tampilkan users yang memiliki mobil dan motor (dibalik sama saja).

select
 users.id as user_id,
 users.username,
 users.job,
 cars.brand as car_brand,
 motorcycles.brand as motorcycle_brand
from users
 LEFT JOIN cars on users.id = cars.user_id
 LEFT JOIN motorcycles on users.id = motorcycles.user_id

 WHERE cars.brand IS NOT NULL AND motorcycles.brand IS NOT NULL;

Tampilkan users yang memiliki mobil saja.

select
 users.id as user_id,
 users.username,
 users.job,
 cars.brand as car_brand,
 motorcycles.brand as motorcycle_brand
from users
 LEFT JOIN cars on users.id = cars.user_id
 LEFT JOIN motorcycles on users.id = motorcycles.user_id

 WHERE cars.brand IS NOT NULL AND motorcycles.brand IS NULL;

Tampilkan users yang memiliki mobil lebih dari 1.

Lihat user dengan user_id 3 dan 4 punya dua mobil.

 select 

     users.id as user_id,

     users.username,

     users.job,

     cars.id,

     cars.brand as car_brand

    FROM users 

     LEFT JOIN cars on users.id = cars.user_id

    where exists (select username, count(*) multiplecars        

    FROM users u

      JOIN cars c on u.id = c.user_id

where users.username = u.username 

     group by 

      u.username 

having count(*) > 1 ) 

User yang memiliki mobil lebih dari satu muncul.

Atau dengan cara lainnya:

select user_id, username, job, id, car_brand
from (select u.id as user_id, u.username, u.job,
             c.id, c.brand as car_brand,
             count(*) over (partition by u.id) as num_cars
      from users u join cars c
           on u.id = c.user_id
     ) uc
where num_cars > 1;

User yang memiliki mobil lebih dari satu muncul.

*Saya sempat bingung dan saya bertanya dan ada yang jawab disini.

Nah, saat studi kasus seperti ini maka kompleksitas mulai muncul dan kita biasanya akan perlu untuk dapat mengkombinasikan berbagai perintah SQL yang ada hingga penggunaan subquery hingga menjadi sebuah solusi.

#todo





Blog post ini akan saya update berkala kedepannya untuk berbagai macam kasus join mulai dari yang sederhana hingga yang ribet.

Sumber gambar dari sini.