Kedua operator menumpuk hasil dua kueri di atas satu sama lain, sehingga SQL UNION vs UNION ALL mungkin terlihat seperti pilihan kecil. Tidak. Satu kata tambahan akan mengubah hasil Anda dan seberapa keras database harus bekerja.

Kami menjalankan setiap kueri di sini di SQLite 3.50, dan hasil serta waktu yang ditampilkan berasal dari proses tersebut. Perilaku dasarnya adalah SQL standar di seluruh database utama. Namun, pengaturan waktunya akan berbeda tergantung database, data, dan perangkat keras Anda, jadi perlakukan waktu kami sebagai ilustrasi.

Jawaban singkatnya

UNION menggabungkan dua kumpulan hasil dan hanya mengembalikan baris berbeda. UNION ALL menggabungkannya dan mengembalikan semuanya, termasuk duplikat. Itulah perbedaannya, dan sisa artikel ini akan membahas mengapa hal itu penting.

UNION vs UNION SEMUA berdampingan

Data sampel

Bayangkan sebuah toko yang menyimpan pesanan online dan di dalam toko dalam tabel terpisah. Perhatikan bahwa Asha memiliki dua pesanan online yang identik, dan satu pesanan serupa di toko.

CREATE TABLE online_orders (customer TEXT, total INT);
CREATE TABLE store_orders (customer TEXT, total INT);

INSERT INTO online_orders VALUES ('Asha',100), ('Ben',200), ('Asha',100);
INSERT INTO store_orders VALUES ('Asha',100), ('Dev',300);

Lihat perbedaannya

SELECT customer, total FROM online_orders
UNION
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Dev  | 300

SELECT customer, total FROM online_orders
UNION ALL
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Asha | 100
-- Asha | 100
-- Dev  | 300

Lima baris masukan menjadi tiga dengan UNION, dan kelimanya tetap dengan UNION ALL. Dua hal yang perlu diperhatikan:

• UNION menghapus duplikat dari seluruh hasil, termasuk yang ada dalam tabel pertama. Ini tidak hanya membandingkan antar tabel.
• Sebuah baris dihitung sebagai duplikat hanya jika setiap kolom yang dipilih cocok.

Jika Anda benar-benar menginginkan daftar yang berbeda, UNION adalah pilihan yang tepat. Hal ini memberikan setiap pelanggan yang membeli melalui salah satu saluran:

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders;

-- Asha
-- Ben
-- Dev

Bug yang salah total

Inilah alasan mengapa pilihan sangat penting. Misalkan Anda menginginkan pendapatan total. Pesanan di atas berjumlah 800. Lihat apa yang diberikan masing-masing operator:

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION
  SELECT total FROM store_orders
);
-- 600

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION ALL
  SELECT total FROM store_orders
);
-- 800

Versi UNION salah sebesar 200. Karena kami hanya memilih kolom total, tiga pesanan terpisah senilai 100 rupee tampak seperti duplikat dan diciutkan menjadi satu. Tidak ada pesan kesalahan, dan 600 tampak seperti angka yang dapat dipercaya. Jenis bug yang tenang ini dapat muncul dalam laporan keuangan selama berbulan-bulan.

Aturan yang harus dipatuhi keduanya

Kedua operator memiliki persyaratan yang sama:

1. Setiap SELECT harus mengembalikan jumlah kolom yang sama.
2. Kolom dicocokkan berdasarkan posisi, bukan berdasarkan nama, jadi jaga agar urutannya tetap sama.
3. Tipe data di setiap posisi harus kompatibel.
4. Nama kolom hasil berasal dari pertama SELECT.
5. ORDER BY berada di bagian paling akhir, dan berlaku untuk keseluruhan hasil.

Melanggar aturan pertama menghasilkan kesalahan. Ini pesan dari SQLite:

SELECT customer, total FROM online_orders
UNION
SELECT customer FROM store_orders;

-- SELECTs to the left and right of UNION do not have
-- the same number of result columns

Aturan kedua lebih berbahaya karena bisa gagal tanpa kesalahan. Jika Anda menukar dua kolom dengan tipe yang sama, seperti first_name dan last_name, kueri berjalan dan data yang dihasilkan salah. SQLite juga santai dalam hal tipe, sedangkan database seperti PostgreSQL lebih ketat dan mungkin memerlukan eksplisit CAST.

Beri label pada sumbernya

Trik yang sangat praktis adalah dengan menambahkan kolom konstan yang menunjukkan dari mana setiap baris berasal. Karena labelnya berbeda, baris dari tabel yang berbeda tidak lagi dapat dianggap sebagai duplikat satu sama lain.

SELECT customer, total, 'online' AS channel FROM online_orders
UNION ALL
SELECT customer, total, 'store' FROM store_orders
ORDER BY customer, channel;

-- Asha | 100 | online
-- Asha | 100 | online
-- Asha | 100 | store
-- Ben  | 200 | online
-- Dev  | 300 | store

Setelah data Anda memiliki label, Anda dapat mengelompokkannya untuk melaporkan penjualan berdasarkan saluran, menggunakan GROUP BY. Jika Anda perlu menggabungkan beberapa langkah, gabungkan gabungan dalam CTE agar tetap dapat dibaca.

Performa: apa yang terjadi dalam pengujian kami

Untuk menghapus duplikat, database harus membandingkan baris, biasanya dengan mengurutkan atau melakukan hashing. UNION ALL dapat melewati langkah ini. Kami membuat dua tabel dengan masing-masing 500.000 baris, berisi angka acak dari 0 hingga 50.000, jadi ada banyak duplikat. Kami menjalankan setiap kueri beberapa kali dan mencatat waktu terbaik.

Pertama, kami menghitung hasilnya, sehingga waktu yang dihabiskan untuk mengirim baris kembali ke program kami tidak mempengaruhi perbandingan:

Kesenjangan yang besar ini sebagian karena SQLite dapat menghitung jumlah tersebut tanpa membaca setiap baris, jadi mohon jangan membacanya karena “ribuan kali lebih cepat”. Namun hal ini menunjukkan gagasan intinya: UNION harus melakukan pekerjaan nyata dan UNION ALL tidak.

Lalu kami mengambil semua baris ke dalam Python, dan hasilnya terbalik:

Di sini, UNION ALL membutuhkan waktu lebih lama karena mengirimkan baris dua puluh kali lebih banyak. Bukan deduplikasi yang memperlambatnya. Menangani satu juta baris adalah. Jadi aturan jujurnya adalah UNION ALL lebih murah untuk dihitung oleh database, tetapi ukuran hasilnya juga penting. Banyak grafik online mengurangi ini menjadi “UNION ALL selalu lebih cepat”, dan pengujian kami menunjukkan mengapa hal ini tidak sesederhana itu.

Jangan memilih berdasarkan kecepatan saja. Pilih operator yang memberikan jawaban benar terlebih dahulu, lalu tune. Jika kueri lambat, baca panduan kami tentang optimasi kueri SQL.

Kapan menggunakan masing-masing

Gunakan UNION ALL ketika:

• Anda menggabungkan data dari tabel serupa, seperti partisi bulanan.
• Anda akan menghitung total atau hitungan setelahnya.
• Anda tahu masukannya tidak boleh tumpang tindih.
• Duplikat mempunyai arti, seperti dalam log atau catatan audit.

Gunakan UNION ketika:

• Anda memerlukan daftar unik, seperti setiap alamat email di dua sistem.
• Sumber yang tumpang tindih memuat fakta yang sama lebih dari satu kali.

Perlakukan UNION ALL sebagai default, dan gunakan UNION dengan sengaja. Menambahkan klausa WHERE ke setiap cabang, untuk memfilter lebih awal, membantu keduanya.

BERSINTANG dan KECUALI

Dua operator himpunan terkait menjawab pertanyaan berbeda. Keduanya mengikuti aturan kolom yang sama:

SELECT customer FROM online_orders
INTERSECT
SELECT customer FROM store_orders;
-- Asha  (bought in both channels)

SELECT customer FROM online_orders
EXCEPT
SELECT customer FROM store_orders;
-- Ben   (online only)

Oracle secara tradisional menggunakan MINUS daripada EXCEPT, dan dukungan untuk operator ini sedikit berbeda menurut database dan versi.

UNION vs GABUNG

Ini sering membingungkan, namun menggabungkan data dalam arah yang berbeda:

Kesalahan umum

• Menggunakan UNION karena kebiasaan. Hal ini dapat menyebabkan Anda kehilangan kecepatan, atau menghapus baris yang valid.
• Memilih terlalu sedikit kolom. Mengabaikan ID membuat baris yang berbeda terlihat identik.
• Urutan kolom tidak cocok. Nilai dapat dimasukkan ke kolom yang salah tanpa kesalahan apa pun.
• Menambahkan ORDER BY ke setiap cabang. Urutkan sekali, di akhir.
• Menggunakan UNION untuk menambal gabungan yang buruk. Jika duplikat muncul karena gabungan, perbaiki gabungan tersebut.
• Dengan asumsi urutan hasil. Tanpa ORDER BY, urutan baris tidak dijamin untuk salah satu operator.

Penutup

Ajukan satu pertanyaan setiap kali: apakah baris yang identik merupakan fakta yang sama, atau fakta yang terpisah? Jika keduanya terpisah, atau jika tumpang tindih tidak mungkin dilakukan, gunakan UNION ALL. Jika Anda memerlukan daftar unik, gunakan UNION. Untuk memeriksa laporan Anda sendiri, cari kode Anda untuk UNION di dekat SUM atau COUNT, dan konfirmasikan bahwa totalnya benar.

Pertanyaan yang sering diajukan

Teruslah belajar