UNION

Rantai toko kopi kita kini punya dua cabang, dan data pelanggan keduanya disimpan di tabel terpisah: pelanggan_jakarta dan pelanggan_bandung. Ketika kantor pusat ingin daftar semua member loyalitas dalam satu laporan, kita menghadapi masalah penggabungan baris, bukan penggabungan kolom. JOIN tidak cocok untuk tugas ini karena JOIN merangkai kolom dari tabel yang berbeda secara horizontal, sedangkan yang kita butuhkan adalah menumpuk baris dari dua sumber secara vertikal. Operator UNION menjawab kebutuhan tersebut: hasil query pertama digabungkan dengan hasil query kedua menjadi satu kumpulan baris. Pada chapter ini kita mempelajari aturan pencocokan kolom berdasarkan posisi, pembuktian bahwa UNION menghilangkan baris duplikat, penempatan ORDER BY yang benar, dan error yang muncul bila aturannya dilanggar.

Menyiapkan Dua Tabel Sumber #

Kita mulai dengan database latihan belajar_set berisi dua tabel berstruktur identik. Kedua tabel menyimpan data member dengan empat kolom yang sama: id, nama, telepon, dan poin:

DROP DATABASE IF EXISTS belajar_set;
CREATE DATABASE belajar_set;
USE belajar_set;

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

CREATE TABLE pelanggan_jakarta (
  id INT PRIMARY KEY,
  nama VARCHAR(50) NOT NULL,
  telepon VARCHAR(20) NOT NULL,
  poin INT NOT NULL
);

INSERT INTO pelanggan_jakarta (id, nama, telepon, poin) VALUES
  (1, 'Budi', '0812-111-2222', 120),
  (2, 'Siti', '0812-333-4444', 85),
  (3, 'Dewi', '0812-555-6666', 200);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

CREATE TABLE pelanggan_bandung (
  id INT PRIMARY KEY,
  nama VARCHAR(50) NOT NULL,
  telepon VARCHAR(20) NOT NULL,
  poin INT NOT NULL
);

INSERT INTO pelanggan_bandung (id, nama, telepon, poin) VALUES
  (1, 'Budi', '0812-111-2222', 120),
  (2, 'Andi', '0821-777-8888', 60),
  (3, 'Rina', '0821-999-0000', 150),
  (4, 'Tono', '0822-123-4567', 95);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Perhatikan bahwa baris Budi sengaja kita masukkan ke kedua tabel dengan nilai yang persis sama di semua kolom — skenario yang realistis karena Budi mendaftar sebagai member di cabang Jakarta dan juga di cabang Bandung. Baris kembar inilah yang menjadi bahan percobaan utama kita. Mari kita lihat dulu isi masing-masing tabel:

SELECT * FROM pelanggan_jakarta ORDER BY id;
SELECT * FROM pelanggan_bandung ORDER BY id;

Output yang Dihasilkan:

+----+------+---------------+------+
| id | nama | telepon       | poin |
+----+------+---------------+------+
|  1 | Budi | 0812-111-2222 |  120 |
|  2 | Siti | 0812-333-4444 |   85 |
|  3 | Dewi | 0812-555-6666 |  200 |
+----+------+---------------+------+
3 rows in set (0.00 sec)

+----+------+---------------+------+
| id | nama | telepon       | poin |
+----+------+---------------+------+
|  1 | Budi | 0812-111-2222 |  120 |
|  2 | Andi | 0821-777-8888 |   60 |
|  3 | Rina | 0821-999-0000 |  150 |
|  4 | Tono | 0822-123-4567 |   95 |
+----+------+---------------+------+
4 rows in set (0.00 sec)

Menggabungkan Hasil Dua Query #

Bentuk paling dasar dari UNION menggabungkan dua query SELECT dengan operator UNION di antaranya:

SELECT id, nama, telepon, poin FROM pelanggan_jakarta
UNION
SELECT id, nama, telepon, poin FROM pelanggan_bandung
ORDER BY nama;

Output yang Dihasilkan:

+----+------+---------------+------+
| id | nama | telepon       | poin |
+----+------+---------------+------+
|  2 | Andi | 0821-777-8888 |   60 |
|  1 | Budi | 0812-111-2222 |  120 |
|  3 | Dewi | 0812-555-6666 |  200 |
|  3 | Rina | 0821-999-0000 |  150 |
|  2 | Siti | 0812-333-4444 |   85 |
|  4 | Tono | 0822-123-4567 |   95 |
+----+------+---------------+------+
6 rows in set (0.00 sec)

Hasilnya enam baris padahal tabel pertama berisi tiga baris dan tabel kedua berisi empat baris. Mari kita bedah apa yang terjadi di balik layar: MySQL menjalankan query pertama dan mendapatkan tiga baris pelanggan Jakarta, lalu menjalankan query kedua dan mendapatkan empat baris pelanggan Bandung. Selanjutnya MySQL menumpuk kedua kumpulan baris menjadi tujuh baris, kemudian menghapus baris-baris yang identik di semua kolomnya — baris Budi dari Bandung lenyap karena kembar persis dengan baris Budi dari Jakarta. Jumlah baris hasil benar-benar dihitung ulang setelah penghapusan duplikat, sehingga 3 + 4 menghasilkan 6, bukan 7. Klausa ORDER BY nama kita letakkan di akhir dan berlaku untuk hasil gabungan, bukan untuk salah satu query saja.

Angka-angka tersebut bisa kita pastikan sendiri dengan menghitung baris tiap sumber dan baris gabungannya dalam satu pernyataan:

SELECT
  (SELECT COUNT(*) FROM pelanggan_jakarta) AS baris_jakarta,
  (SELECT COUNT(*) FROM pelanggan_bandung) AS baris_bandung,
  (SELECT COUNT(*) FROM (
     SELECT id, nama, telepon, poin FROM pelanggan_jakarta
     UNION
     SELECT id, nama, telepon, poin FROM pelanggan_bandung
   ) AS gabungan) AS baris_gabungan;

Output yang Dihasilkan:

+---------------+---------------+----------------+
| baris_jakarta | baris_bandung | baris_gabungan |
+---------------+---------------+----------------+
|             3 |             4 |              6 |
+---------------+---------------+----------------+
1 row in set (0.00 sec)

Satu baris hasil menegaskan hitungannya: sumber pertama 3, sumber kedua 4, hasil UNION 6. Selisih satu baris itulah baris kembar Budi yang dilebur. Perhitungan seperti ini berguna saat kedua sumber berubah-ubah — misalnya data member ditambah tiap minggu — karena jumlah baris hasil UNION tidak pernah bisa ditebak hanya dari menjumlahkan kedua sumber.

Kolom Dicocokkan Berdasarkan Posisi #

Pencocokan kolom pada UNION mengikuti posisi, bukan nama. Nama kolom hasil final diambil dari query pertama, sedangkan query kedua cukup menyediakan kolom-kolom dengan tipe data yang kompatibel pada urutan yang sama. Kita buktikan dengan memberi alias berbeda pada query pertama:

SELECT nama AS nama_pelanggan, poin FROM pelanggan_jakarta
UNION
SELECT nama, poin FROM pelanggan_bandung
ORDER BY poin DESC;

Output yang Dihasilkan:

+----------------+------+
| nama_pelanggan | poin |
+----------------+------+
| Dewi           |  200 |
| Rina           |  150 |
| Budi           |  120 |
| Tono           |   95 |
| Siti           |   85 |
| Andi           |   60 |
+----------------+------+
6 rows in set (0.00 sec)

Kolom hasil tampil dengan judul nama_pelanggan walau query kedua tetap menulis nama tanpa alias. MySQL memasangkan nama dari Jakarta dengan nama dari Bandung karena keduanya menempati posisi pertama, lalu poin dengan poin karena sama-sama di posisi kedua. Konsekuensi aturan posisi ini harus diwaspadai: bila urutan kolom query kedua tertukar, nilai poin bisa menempel pada kolom nama tanpa terdeteksi sebagai error selama tipe datanya masih kompatibel. Karena itu kebiasaan terbaik kita adalah selalu menuliskan kolom dalam urutan yang sama dan memastikan tipenya sepadan, misalnya INT dengan INT atau VARCHAR dengan VARCHAR.

Bila jumlah kolom kedua query tidak sama, MySQL langsung menolak sebelum eksekusi berjalan:

-- @error
SELECT id, nama, telepon, poin FROM pelanggan_jakarta UNION SELECT id, nama FROM pelanggan_bandung;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1222 (21000): The used SELECT statements have a different number of columns
Bye

Server membalas dengan ERROR 1222 karena query pertama menyediakan empat kolom sementara query kedua hanya dua. Struktur kedua kumpulan hasil harus cocok sejak dari awal — itulah sebabnya dua tabel sumber kita memang sengaja dibuat dengan definisi kolom yang sama.

ORDER BY untuk Hasil Gabungan #

Klausa ORDER BY pada UNION hanya boleh ditulis satu kali di bagian paling akhir, dan klausa tersebut mengurutkan hasil gabungan final. Mencoba mengurutkan query pertama secara terpisah dengan menaruh ORDER BY sebelum kata UNION justru ditolak parser:

-- @error
SELECT id, nama FROM pelanggan_jakarta ORDER BY nama UNION SELECT id, nama FROM pelanggan_bandung;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT id, nama FROM pelanggan_bandung' at line 1
Bye

Pernyataan di atas gagal dengan error sintaks karena tata bahasa SQL tidak mengizinkan ORDER BY berdiri di tengah ungkapan UNION. Bila kita benar-benar perlu membatasi setiap sumber secara terpisah — misalnya mengambil dua pelanggan dengan poin tertinggi dari tiap cabang — solusinya adalah membungkus setiap query dengan tanda kurung dan menempatkan ORDER BY berdampingan dengan LIMIT di dalam kurung tersebut:

(SELECT nama, poin FROM pelanggan_jakarta ORDER BY poin DESC LIMIT 2)
UNION
(SELECT nama, poin FROM pelanggan_bandung ORDER BY poin DESC LIMIT 2)
ORDER BY poin DESC;

Output yang Dihasilkan:

+------+------+
| nama | poin |
+------+------+
| Dewi |  200 |
| Rina |  150 |
| Budi |  120 |
+------+------+
3 rows in set (0.00 sec)

Cabang Jakarta menyumbang Dewi (200 poin) dan Budi (120 poin), sedangkan cabang Bandung menyumbang Rina (150 poin) dan Budi (120 poin). Operator UNION kemudian melebur dua baris Budi yang identik menjadi satu, sehingga hasil akhirnya tiga baris. Tanpa penghapusan duplikat, LIMIT 2 per cabang akan menghasilkan empat baris — perilaku UNION ALL yang akan kita bahas tuntas di chapter berikutnya.

Membersihkan Database Latihan #

Rangkaian percobaan selesai dan database latihan kita jatuhkan kembali:

DROP DATABASE belajar_set;

Output yang Dihasilkan:

Query OK, 2 rows affected (0.00 sec)

Referensi #

• UNION Clause — MySQL 8.4 Reference Manual
• SELECT Statement — MySQL 8.4 Reference Manual

Kita kini bisa menumpuk hasil beberapa query menjadi satu laporan dengan UNION, lengkap dengan pemahaman posisi kolom, penghapusan duplikat, dan penempatan ORDER BY yang benar. Namun penghapusan duplikat tadi terasa sebagai kerja ekstra — MySQL harus membandingkan seluruh baris sebelum boleh menampilkan hasil. Bila duplikat justru ingin dipertahankan, misalnya laporan yang mencatat setiap pesanan apa adanya, operator UNION ALL bekerja lebih sederhana dan lebih cepat; chapter berikutnya membedah keduanya sampai tuntas.