Menyusun Kueri — Memadukan JOIN, Subkueri, dan UNION

Data yang dipakai di artikel ini — employee / department / sales

JOIN (menghubungkan beberapa tabel), subkueri (kueri di dalam kueri), dan UNION (menumpuk hasil secara vertikal) masing-masing bekerja sendiri, tapi ketika kamu memadukannya dengan WHERE / ORDER BY / LIMIT kamu bisa menulis kueri praktis dunia nyata.

Di artikel ini kamu akan melalui, secara berurutan, cara memadukan teknik-teknik ini dan menumbuhkannya menjadi satu kueri.

Materinya adalah tiga tabel karyawan — employee (30 karyawan), department (6 departemen), dan sales (50 baris penjualan).

Kamu akan mulai dengan JOIN sederhana, lalu membangun melalui subkueri dan UNION langkah demi langkah.

Sebelum menyelami latihan, sempatkan memeriksa definisi kolom dan contoh data dari tiga tabel yang dipakai di artikel ini — employee / department / sales.

① Pakai PRAGMA table_info(...) untuk memeriksa definisi kolom ketiga tabel.

② Pakai SELECT * FROM nama_tabel LIMIT 5; untuk melihat 5 baris pertama tiap tabel.

Pastikan urutan klausa dievaluasi — JOIN, WHERE, ORDER BY, LIMIT

Urutan kamu menulis kueri (SELECT … FROM … WHERE … ORDER BY) berbeda dari urutan ia dievaluasi.

Database pertama menghubungkan tabel dengan FROM / JOIN, lalu mempersempit baris dengan WHERE, mengagregasi dengan GROUP BY / HAVING, membangun kolom dengan SELECT, dan terakhir mengurutkan dengan ORDER BY serta membatasi jumlah baris dengan LIMIT.

Begitu kamu menguasai urutan ini, aturan seperti "untuk menyaring hasil agregat pakai HAVING, bukan WHERE" dan "LIMIT berlaku setelah pengurutan" mulai masuk akal.

-- JOIN + WHERE + ORDER BY: karyawan di departemen Osaka, diurutkan berdasarkan tanggal masuk
SELECT emp.name, dept.dept_name, emp.hired_on
FROM employee emp
JOIN department dept ON dept.dept_id = emp.dept_id
WHERE dept.location = 'Osaka'
ORDER BY emp.hired_on;

Padukan JOIN, WHERE, ORDER BY, dan LIMIT dalam satu kueri. (Jalankan dengan benar dan penjelasannya akan muncul.)

① Inner join employee dan department berdasarkan dept_id dan ambil nama karyawan, nama departemen, dan gaji.

② Persempit ke hanya karyawan yang lokasi departemennya (department.location) adalah Tokyo.

③ Urutkan berdasarkan gaji dari tertinggi ke terendah dan ambil hanya 5 teratas.

Bangun nilai acuan dengan subkueri dan persempit hasil JOIN

Subkueri bisa dipakai untuk membangun "nilai acuan" yang kamu butuhkan untuk mempersempit lebih jauh hasil yang kamu rakit dengan JOIN.

Nilai kriteria yang berubah per karyawan, seperti "rata-rata gaji departemen saya sendiri," dihitung dengan subkueri terkorelasi (subkueri yang dihitung ulang untuk setiap karyawan) yang mengacu ke baris luar.

Ketika kamu menempelkan nama departemen dengan JOIN dan mempersempit baris memakai kondisi subkueri terkorelasi, beberapa potongan sintaks bertaut dalam satu kueri.

-- JOIN + subkueri terkorelasi: karyawan yang gajinya sama dengan gaji maksimum departemennya sendiri (yang tertinggi di departemen)
SELECT emp.name, dept.dept_name, emp.salary
FROM employee emp
JOIN department dept ON dept.dept_id = emp.dept_id
WHERE emp.salary = (
  SELECT MAX(dept_member.salary)
  FROM employee dept_member
  WHERE dept_member.dept_id = emp.dept_id
);

Kali ini kamu akan menumpuk subkueri. Daftar, dengan nama departemen, karyawan di tiap departemen yang bergaji "lebih dari rata-rata gaji departemennya sendiri."

① Join employee dan department dan ambil nama karyawan, nama departemen, dan gaji.

② Pakai subkueri terkorelasi untuk menghitung rata-rata gaji departemen karyawan itu sendiri, dan persempit ke hanya karyawan yang salary-nya lebih besar darinya.

③ Urutkan berdasarkan dept_id menaik, dan dalam departemen yang sama berdasarkan gaji dari tertinggi ke terendah.

Padukan dua sudut pandang dengan UNION dan akhiri dengan ORDER BY

UNION menumpuk hasil dua SELECT secara vertikal.

SELECT atas dan bawah harus kompatibel UNION (jumlah kolom sama, dengan tipe kolom yang bersesuaian kompatibel).

UNION meleburkan baris yang sama persis menjadi satu (membuang duplikat), sementara UNION ALL mempertahankan duplikat apa adanya.

Pengurutan dan batas jumlah baris berlaku untuk hasil gabungan secara keseluruhan, jadi tulis ORDER BY / LIMIT hanya sekali, di paling akhir.

-- UNION ALL: tumpuk dua sudut pandang secara vertikal dengan kolom label (pertahankan duplikat)
SELECT name, 'Kyoto' AS via FROM employee WHERE city = 'Kyoto'
UNION ALL
SELECT emp.name, 'HighSales' AS via
FROM employee emp
JOIN sales sale ON sale.emp_id = emp.emp_id
GROUP BY emp.emp_id
HAVING SUM(sale.amount) >= 1500000
ORDER BY name;

Terakhir, padukan dua sudut pandang menjadi satu daftar dengan UNION.

① Tulis SELECT yang mengambil nama "karyawan yang tinggal di Kyoto."

② Tulis SELECT yang menemukan "karyawan yang total penjualannya 1,5 juta yen atau lebih" memakai join employee dan sales plus agregasi (GROUP BY / HAVING).

③ Hubungkan keduanya dengan UNION untuk meleburkan duplikat menjadi satu baris, dan urutkan berdasarkan nama menaik.