Operasi Himpunan — UNION / INTERSECT / EXCEPT

Data yang dipakai di artikel ini — employee dan contractor

Operasi himpunan (operasi yang menggabungkan beberapa hasil SELECT menjadi satu sebagai gabungan, irisan, atau selisih himpunan) adalah sintaks untuk menumpuk hasil yang diambil terpisah secara vertikal, atau mengambil baris yang sama-sama ada di keduanya atau baris yang hanya muncul di salah satu sisi.

Kamu akan bekerja dengan empat di antaranya: UNION (gabungan), UNION ALL (gabungan yang mempertahankan duplikat), INTERSECT (irisan), dan EXCEPT (selisih).

Materinya adalah tabel karyawan employee (30 baris) dan tabel kontraktor contractor (6 baris).

Kamu akan mencoba keempat operasi himpunan satu per satu — menggabungkan dua hasil dengan kondisi berbeda di dalam employee, atau memadukan hasil yang diambil dari tabel berbeda seperti employee dan contractor menjadi satu.

Sebelum menyelami latihan, lihatlah definisi kolom dan contoh data dari dua tabel yang dipakai artikel ini — employee dan contractor.

① Jalankan PRAGMA table_info(employee); dan PRAGMA table_info(contractor); untuk memeriksa definisi kolom kedua tabel.

② Jalankan SELECT * FROM employee LIMIT 5; dan SELECT * FROM contractor LIMIT 5; untuk melihat 5 baris pertama data.

Premis operasi himpunan — menjadi kompatibel UNION

Keempat operasi himpunan menggabungkan dua hasil SELECT atau lebih dalam bentuk SELECT ... operator SELECT ....

Premis untuk menggabungkan adalah SELECT atas dan bawah kompatibel UNION (jumlah kolom sama, dan tipe kolom yang bersesuaian kompatibel).

SELECT name FROM employee dan SELECT name FROM contractor keduanya adalah satu kolom string, jadi kompatibel.

Di sisi lain, SELECT name FROM employee dan SELECT name, city FROM contractor punya jumlah kolom berbeda, jadi kamu tidak bisa menggabungkannya.

-- kompatibel UNION: kedua sisi 2 kolom (name, city)
SELECT name, city FROM employee WHERE city = 'Tokyo'
UNION
SELECT name, city FROM contractor
ORDER BY name;

-- ORDER BY hanya sekali di paling akhir
-- kamu tidak bisa menulis ORDER BY per-SELECT di tengah

UNION dan UNION ALL — buang duplikat atau pertahankan

UNION menumpuk dua hasil secara vertikal dan menggabungkan baris yang sama persis menjadi satu (penghapusan duplikat).

UNION ALL tidak melakukan penghapusan duplikat dan mempertahankan setiap baris dari kedua sisi apa adanya.

Ketika kamu tidak perlu membuang duplikat, atau ketika kamu ingin mempertahankan "berapa banyak baris yang muncul di keduanya," pakai UNION ALL.

UNION menjalankan pengurutan internal untuk mendeteksi duplikat, jadi UNION ALL adalah operasi yang lebih ringan.

Contoh di bawah menggabungkan hasil "tinggal di Kyoto" dan hasil "gaji 7 juta atau lebih" di dalam employee.

Karyawan yang memenuhi kedua kondisi menjadi 1 baris dengan UNION, dan 2 baris dengan UNION ALL.

-- UNION: baris duplikat digabungkan menjadi satu
SELECT name FROM employee WHERE city = 'Osaka'
UNION
SELECT name FROM employee WHERE salary >= 5000000
ORDER BY name;

-- UNION ALL: duplikat dipertahankan (baris yang cocok keduanya muncul dua kali)
SELECT name FROM employee WHERE city = 'Osaka'
UNION ALL
SELECT name FROM employee WHERE salary >= 5000000
ORDER BY name;

Bayangkan kebutuhan: "saya ingin menggabungkan karyawan yang tinggal di Kyoto dan karyawan dengan gaji tinggi menjadi satu daftar. Siapa pun yang cocok keduanya harus muncul hanya sekali." (Jalankan dengan benar dan penjelasannya akan muncul.)

① Tulis SELECT yang menarik name dari employee di mana city adalah Kyoto.

② Gabungkan dengan SELECT yang menarik name baris yang salary-nya 7.000.000 atau lebih, memakai operator himpunan yang membuang duplikat.

③ Urutkan hasil berdasarkan name menaik (untuk menjamin urutan baris).

Bayangkan kebutuhan: "saya ingin menumpuk hasil dari dua kondisi yang sama seperti Latihan 1 tanpa membuang duplikat — semuanya — supaya saya bisa memastikan secara visual nama mana yang muncul di keduanya."

① Gabungkan dua SELECT yang sama seperti Latihan 1 (name untuk tinggal di Kyoto / gaji 7.000.000 atau lebih) memakai operator himpunan yang mempertahankan duplikat.

② Urutkan hasil berdasarkan name menaik. Pastikan nama yang muncul di keduanya tampil sebagai dua baris berturut-turut.

INTERSECT dan EXCEPT — baris yang sama dan baris selisih

• INTERSECT (irisan) mengembalikan hanya baris yang muncul sama-sama di kedua hasil.
• EXCEPT (selisih) mengembalikan baris yang ada di hasil atas tapi tidak ada di hasil bawah.

Keduanya otomatis membuang duplikat (perilaku yang sama dengan UNION).

EXCEPT adalah operasi asimetris yang hasilnya berubah jika kamu menukar sisi atas dan bawah — A EXCEPT B dan B EXCEPT A adalah hal yang berbeda.

Contoh di bawah menggabungkan name untuk "tinggal di Kyoto" dan "gaji 6,5 juta atau lebih" di dalam employee dengan INTERSECT, mengambil karyawan yang memenuhi keduanya (baris yang sama).

-- INTERSECT: karyawan yang tinggal di Osaka DAN dengan gaji 6000000 atau lebih
SELECT name FROM employee WHERE city = 'Osaka'
INTERSECT
SELECT name FROM employee WHERE salary >= 6000000
ORDER BY name;

-- EXCEPT: karyawan yang tinggal di Osaka tapi BUKAN dengan gaji 6000000 atau lebih
SELECT name FROM employee WHERE city = 'Osaka'
EXCEPT
SELECT name FROM employee WHERE salary >= 6000000
ORDER BY name;

Selesaikan kebutuhan "saya ingin mengambil hanya karyawan yang tinggal di Kyoto DAN dengan gaji 6.500.000 atau lebih" memakai operasi himpunan.

① Tulis SELECT yang menarik name dari employee di mana city adalah Kyoto.

② Gabungkan dengan SELECT yang menarik name baris yang salary-nya 6.500.000 atau lebih, memakai operator himpunan yang hanya mengembalikan baris yang sama.

③ Urutkan hasil berdasarkan name menaik.

Bayangkan kebutuhan: "dari karyawan yang tinggal di Kyoto, saya ingin mengambil hanya mereka yang BUKAN bergaji 6.500.000 atau lebih (warga Kyoto yang bergaji lebih rendah)."

① Tulis SELECT yang menarik name dari employee di mana city adalah Kyoto.

② Gabungkan dengan SELECT yang menarik name baris yang salary-nya 6.500.000 atau lebih, memakai operator himpunan yang mengembalikan baris di sisi atas tapi tidak di sisi bawah (hati-hati — urutan sisi atas dan bawah memengaruhi hasil).

③ Urutkan hasil berdasarkan name menaik.

Operasi himpunan lintas tabel berbeda — employee dan contractor

Operasi himpunan bekerja tidak hanya di dalam satu tabel tapi juga antara hasil yang diambil dari tabel terpisah.

Selama keduanya kompatibel UNION (jumlah dan tipe kolom cocok), tabelnya boleh berbeda.

Ketika kamu ingin mengurutkan hasil gabungan, tulis satu ORDER BY setelah SELECT paling akhir (ia berlaku untuk seluruh hasil gabungan).

Menggabungkan name tabel karyawan employee dan tabel kontraktor contractor dengan INTERSECT memungkinkanmu memunculkan orang yang muncul di keduanya dengan nama yang sama.

Contoh di bawah menarik hanya satu kolom name dan menggabungkannya dengan INTERSECT.

Karena name kedua tabel punya Alice dan Bob, 2 orang dikembalikan sebagai baris yang sama.

-- nama yang muncul di employee dan contractor
SELECT name FROM employee
INTERSECT
SELECT name FROM contractor
ORDER BY name;

-- nama di contractor tapi tidak di employee (hanya eksternal)
SELECT name FROM contractor
EXCEPT
SELECT name FROM employee
ORDER BY name;

Bayangkan kebutuhan: "saya ingin mendaftar orang di tabel kontraktor contractor yang nama samanya TIDAK ada di tabel karyawan employee (nama yang murni hanya eksternal)."

① Tulis SELECT yang menarik name dari contractor.

② Gabungkan dengan SELECT yang menarik name dari employee, memakai operator himpunan yang mengembalikan baris di sisi atas tapi tidak di sisi bawah. Taruh SELECT contractor di atas.

③ Urutkan hasil berdasarkan name menaik.