Membuat relasi antar tabel dengan Inner atau left join

LEFT join

left join digunakan bila ingin menampilkan seluruh data dari tabel pertama, tanpa memperdulikan tabel kedua punya relasi atau tidak
misalnya tabel customer dan rental
- 1 customer bisa sewa beberapa kali film
- namun ada customer yang hanya daftar saja tapi belum pernah sewa
- bila kita menggunakan inner join yang tampil hanya customer yang pernah menyewa saja
- bila menggunakan left join bisa ditampilkan semua customer baik yang pernah sewa atau belum pernah.
- untuk customer yang belum pernah menyewa maka data rental_idnya adalah NULL

    INSERT INTO customer (store_id, first_name, last_name,email, address_id)
    VALUES (1,'yudy','widjaja','widjajaydles@gmail.com',5);

perintah diatas menyisipkan 1 customer baru tanpa pernah menyewa
perintah dibawah ini akan mencari semua customer yang belum pernah menyewa.
perhatikan penggunaan IS NULL bukan = NULL

    SELECT customer.first_name, rental.rental_id
    FROM customer
    LEFT JOIN rental ON customer.customer_id = rental.customer_id
    WHERE rental.rental_id IS NULL;

coba bandingkan bila menggunakan perintah INNER JOIN seperti dibawah ini.

    SELECT customer.first_name, rental.rental_id
    FROM customer
    INNER JOIN rental ON customer.customer_id = rental.customer_id
    WHERE rental.rental_id IS NULL;

Self join

terkadang satu tabel di-join dengan dirinya sendiri (self)
contoh di dunia nyata misalnya tabel pegawai, diisi dengan semua pegawai di perusahaan tersebut
di tabel pegawai terdapat kolom manager_id, dimana kolom ini berisi id dari atasan pegawai tersebut
contoh dari self join yang lain
tampilkan semua film yang panjang durasi filmnya sama (length)

    SELECT f1.title, f2.title, f1.length
    FROM   film f1
    INNER JOIN film f2 ON f1.film_id <> f2.film_id AND f1.length = f2.length;

penjelasan
- From film f1 artinya tabel film diberi alias f1
- inner join ke film f2 artinya yang di join juga film dan diberi nama alias f2
- tampilkan yang panjang filmnya sama (f1.length = f2.length) tapi idnya tidak boleh sama

Soal

tampilkan dari tabel customer, kolom customer_id,first_name dan email,
urutkan tampilan berdasarkan customer_id secara Ascending,
batasi tampilan data dengan LIMIT hanya 5 data saja
tampilkan dari tabel customer, kolom customer_id,first_name dan email,
5 data terakhir
tampilkan dari tabel customer, kolom first_name dan addressnya,
perlu diketahui alamat customer tidak di tabel yang sama,
perhatikan gambar relationship tables yang diberikan,
batasi tampilan data 5 baris saja diurutkan dengan first_name ASC
(bantuan: gunakan perintah INNER JOIN address ON customer.address_id = address.address_id)
tampilkan semua category film dari tabel category
tampilkan semua film dengan category "Horror"
kolom yang ditampilkan judul film (film.title), dan categorynya (category.name)
petunjuk data diambil dari tabel category,
kemudian lakukan INNER JOIN dari tabel category ke film_category
kemudian lakukan INNER JOIN dari tabel film_category ke film
untuk filter gunakan perintah where
tampilkan semua film yang dibuat pada tahun 2006 (film.release_year)
kolom yang ditampilkan film.title, film.release_year dan nama categorynya (category.name)
tampilkan semua nama film yang pernah disewa oleh Jared Ely
kolom yang ditampilkan adalah customer.first_name, customer.last_name dan film.title
cara aksesnya dari customer ke tabel rental, kemudian ke tabel inventory baru ke tabel film
gunakan perintah INNER JOIN dan WHERE
(bila jawaban benar ada 19 film)
tampilkan customer(first_name), nama film dan tanggal sewanya dimana tanggal sewa antara 25-mei-2005 sampai 27-mei-2005 dan category film adalah horror

Jawab

soal 1

  SELECT customer_id, first_name, email
  FROM customer
  ORDER BY customer_id
  OFFSET 10 ROWS
  FETCH FIRST 5 ROW ONLY;

soal 2

  SELECT customer_id, first_name, email
  FROM customer
  ORDER BY customer_id DESC
  FETCH FIRST 5 ROW ONLY;

soal 3

  SELECT customer_id, customer.first_name, address.address
  FROM customer
  INNER JOIN address ON customer.address_id = address.address_id
  ORDER BY customer.first_name ASC
  FETCH FIRST 5 ROW ONLY;

soal 4
```
  select * from category;
```

soal 5

  SELECT category.name, film.title
  FROM category
  INNER JOIN film_category ON category.category_id = film_category.category_id
  INNER JOIN film          ON film_category.film_id = film.film_id
  where category.name = 'Horror';

soal 6

  select film.title, film.release_year, category.name
  from film
  inner join film_category ON film.film_id = film_category.film_id
  inner join category      ON film_category.category_id = category.category_id
  where film.release_year = 2006;

  SELECT category.name, film.title, film.release_year
  FROM category
  INNER JOIN film_category ON category.category_id = film_category.category_id
  INNER JOIN film          ON film_category.film_id = film.film_id
  where film.release_year = 2006;

soal 7

  SELECT customer.first_name, customer.last_name 
  FROM customer
  FETCH FIRST ROW ONLY;

  SELECT customer.first_name, customer.last_name, film.title
  FROM customer
  INNER JOIN rental ON customer.customer_id = rental.customer_id
  INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
  INNER JOIN film      ON inventory.film_id   = film.film_id
  WHERE customer.first_name = 'Jared' AND customer.last_name = 'Ely';

soal 8

SELECT c.first_name, f.title, r.rental_date, cat.name
FROM customer c
INNER JOIN rental r ON c.customer_id = r.customer_id
INNER JOIN inventory i ON r.inventory_id = i.inventory_id
INNER JOIN film f ON f.film_id = i.film_id
INNER JOIN film_category fc ON f.film_id = fc.film_id
INNER JOIN category cat ON cat.category_id = fc.category_id
WHERE r.rental_date >= '2005-05-25' AND r.rental_date <= '2005-05-27' AND cat.name = 'Horror';