Contoh Penggunaan UNION ALL

union-all.sql

D:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed

mysql> -- -----------------------------------
mysql> -- create table 1
mysql> -- -----------------------------------
mysql> CREATE TABLE IF NOT EXISTS `tanggal` (
    ->   `nama` varchar(100) NOT NULL,
    ->   `tgl1` date NOT NULL,
    ->   `tgl2` date NOT NULL,
    ->   PRIMARY KEY (`nama`)
    -> ) ;
Query OK, 0 rows affected (0.05 sec)

mysql> -- -----------------------------------
mysql> -- insert data ke table 1
mysql> -- -----------------------------------
mysql> INSERT INTO `tanggal` (`nama`, `tgl1`, `tgl2`)
    -> VALUES
    ->   ('bejo', '2013-06-29', '2013-07-03'),
    ->   ('surti', '2013-07-01', '2013-07-05') ;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> -- -----------------------------------
mysql> -- tampilkan data di table 1
mysql> -- -----------------------------------
mysql> select * from tanggal ;
+-------+------------+------------+
| nama  | tgl1       | tgl2       |
+-------+------------+------------+
| bejo  | 2013-06-29 | 2013-07-03 |
| surti | 2013-07-01 | 2013-07-05 |
+-------+------------+------------+
2 rows in set (0.01 sec)

mysql> -- -----------------------------------
mysql> -- ini logika dasarnya
mysql> -- menggunakan UNION ALL
mysql> -- -----------------------------------
mysql> select nama, tgl1 from tanggal
    -> union all
    -> select nama, tgl2 from tanggal ;
+-------+------------+
| nama  | tgl1       |
+-------+------------+
| bejo  | 2013-06-29 |
| surti | 2013-07-01 |
| bejo  | 2013-07-03 |
| surti | 2013-07-05 |
+-------+------------+
4 rows in set (0.00 sec)

mysql> -- -----------------------------------
mysql> -- create table 2
mysql> -- -----------------------------------
mysql> CREATE TABLE IF NOT EXISTS `tanggal2` (
    ->   `nama` varchar(100) NOT NULL,
    ->   `tgl1` date NOT NULL,
    ->   `tgl2` date NOT NULL,
    ->   PRIMARY KEY (`nama`)
    -> ) ;
Query OK, 0 rows affected (0.01 sec)

mysql> -- -----------------------------------
mysql> -- tampilkan isi table 2
mysql> -- -----------------------------------
mysql> select * from tanggal2;
+-------+------------+------------+
| nama  | tgl1       | tgl2       |
+-------+------------+------------+
| bejo  | 2013-05-01 | 2013-05-03 |
| surti | 2013-07-09 | 2013-07-10 |
| toni  | 2013-04-18 | 2013-04-23 |
+-------+------------+------------+
3 rows in set (0.00 sec)

mysql> -- -----------------------------------
mysql> -- insert data ke table 2
mysql> -- -----------------------------------
mysql> INSERT INTO `tanggal2` (`nama`, `tgl1`, `tgl2`)
    -> VALUES
    ->   ('toni', '2013-04-18', '2013-04-23'),
    ->   ('bejo', '2013-05-01', '2013-05-03'),
    ->   ('surti', '2013-07-09', '2013-07-10') ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> -- -----------------------------------
mysql> -- create table 3
mysql> -- -----------------------------------
mysql> CREATE TABLE IF NOT EXISTS `tanggal3` (
    ->   `nama` varchar(100) NOT NULL,
    ->   `tgl1` date NOT NULL,
    ->   PRIMARY KEY (`nama`)
    -> ) ;
Query OK, 0 rows affected (0.01 sec)

mysql> -- -----------------------------------
mysql> -- insert data ke table 3
mysql> -- -----------------------------------
mysql> INSERT INTO `tanggal3` (`nama`, `tgl1`)
    -> VALUES
    ->   ( 'nurul', '2013-04-18' ),
    ->   ( 'endang', '2013-05-01' ),
    ->   ( 'dono', '2013-07-01' ),
    ->   ( 'kasino', '2013-07-01' ),
    ->   ( 'indro', '2013-07-02' ) ;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> -- -----------------------------------
mysql> -- tampilkan isi table 3
mysql> -- -----------------------------------
mysql> select * from tanggal3;
+--------+------------+
| nama   | tgl1       |
+--------+------------+
| dono   | 2013-07-01 |
| endang | 2013-05-01 |
| indro  | 2013-07-02 |
| kasino | 2013-07-01 |
| nurul  | 2013-04-18 |
+--------+------------+
5 rows in set (0.00 sec)

mysql> -- -----------------------------------
mysql> -- sekarang buat query UNION
mysql> -- yang akan menggabungkan tiga tabel di atas
mysql> -- -----------------------------------
mysql> select nama, tgl1 from tanggal
    -> union all
    -> select nama, tgl2 from tanggal
    -> union all
    -> select nama, tgl1 from tanggal2
    -> union all
    -> select nama, tgl2 from tanggal2
    -> union all
    -> select nama, tgl1 from tanggal3 ;
+--------+------------+
| nama   | tgl1       |
+--------+------------+
| bejo   | 2013-06-29 |
| surti  | 2013-07-01 |
| bejo   | 2013-07-03 |
| surti  | 2013-07-05 |
| bejo   | 2013-05-01 |
| surti  | 2013-07-09 |
| toni   | 2013-04-18 |
| bejo   | 2013-05-03 |
| surti  | 2013-07-10 |
| toni   | 2013-04-23 |
| dono   | 2013-07-01 |
| endang | 2013-05-01 |
| indro  | 2013-07-02 |
| kasino | 2013-07-01 |
| nurul  | 2013-04-18 |
+--------+------------+
15 rows in set (0.00 sec)

mysql> -- -----------------------------------
mysql> -- Sekarang tambahkan kolom "keterangan"
mysql> -- -----------------------------------
mysql> select 'Table #1' as keterangan, nama, tgl1 from tanggal
    -> union all
    -> select 'Table #1' as keterangan, nama, tgl2 from tanggal
    -> union all
    -> select 'Table #2' as keterangan, nama, tgl1 from tanggal2
    -> union all
    -> select 'Table #2' as keterangan, nama, tgl2 from tanggal2
    -> union all
    -> select 'Table #3' as keterangan, nama, tgl1 from tanggal3 ;
+------------+--------+------------+
| keterangan | nama   | tgl1       |
+------------+--------+------------+
| Table #1   | bejo   | 2013-06-29 |
| Table #1   | surti  | 2013-07-01 |
| Table #1   | bejo   | 2013-07-03 |
| Table #1   | surti  | 2013-07-05 |
| Table #2   | bejo   | 2013-05-01 |
| Table #2   | surti  | 2013-07-09 |
| Table #2   | toni   | 2013-04-18 |
| Table #2   | bejo   | 2013-05-03 |
| Table #2   | surti  | 2013-07-10 |
| Table #2   | toni   | 2013-04-23 |
| Table #3   | dono   | 2013-07-01 |
| Table #3   | endang | 2013-05-01 |
| Table #3   | indro  | 2013-07-02 |
| Table #3   | kasino | 2013-07-01 |
| Table #3   | nurul  | 2013-04-18 |
+------------+--------+------------+
15 rows in set (0.02 sec)

mysql> -- -----------------------------------
mysql> -- Sekarang ganti nama kolomny
mysql> -- perhatikan bagian "as ...."
mysql> -- -----------------------------------
mysql> select 'Table #1' as keterangan, nama, tgl1 as tglresume
    -> from tanggal union all
    -> select 'Table #1' as keterangan, nama, tgl2 as tglresume
    -> from tanggal union all
    -> select 'Table #2' as keterangan, nama, tgl1 as tglresume
    -> from tanggal2 union all
    -> select 'Table #2' as keterangan, nama, tgl2 as tglresume
    -> from tanggal2 union all
    -> select 'Table #3' as keterangan, nama, tgl1 as tglresume
    -> from tanggal3 ;
+------------+--------+------------+
| keterangan | nama   | tglresume  |
+------------+--------+------------+
| Table #1   | bejo   | 2013-06-29 |
| Table #1   | surti  | 2013-07-01 |
| Table #1   | bejo   | 2013-07-03 |
| Table #1   | surti  | 2013-07-05 |
| Table #2   | bejo   | 2013-05-01 |
| Table #2   | surti  | 2013-07-09 |
| Table #2   | toni   | 2013-04-18 |
| Table #2   | bejo   | 2013-05-03 |
| Table #2   | surti  | 2013-07-10 |
| Table #2   | toni   | 2013-04-23 |
| Table #3   | dono   | 2013-07-01 |
| Table #3   | endang | 2013-05-01 |
| Table #3   | indro  | 2013-07-02 |
| Table #3   | kasino | 2013-07-01 |
| Table #3   | nurul  | 2013-04-18 |
+------------+--------+------------+
15 rows in set (0.00 sec)

mysql> -- -----------------------------------
mysql> -- YAY! Success!
mysql> -- -----------------------------------
mysql>