Union Clause Pada Oracle SQL

Apa Itu Union?

Union (Gabungan) adalah gabungan dua tabel atau lebih yang membentuk sebuah irisan,

Sintak Dasar

Di bawah ini adalah sintak dasar penggunaan Union clause pada Oracle SQL:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Contoh

Asumsikan Anda telah mempunyai dua tabel yaitu tabel Customer dan tabel Orders sebagai contoh dengan data-data berikut:

Tabel Customer

SQL> SELECT * FROM customer;

CUSTOMER_ID CUSTOMER_NAME     CUSTOMER_ADDRESS
----------- ----------------- -----------------
CS001       Nursalim          Bandung
CS002       Nani Indriyani    Jakarta
CS003       Naura Krasiva     Surabaya
CS004       Ahmad Fatoni      Semarang
CS005       Sri Rahayu        Yogyakarta

Tabel Orders

SQL> SELECT * FROM orders;

ORDER_ID ORDER_DATE CUSTOMER_ID QTY   AMOUNT
-------- ---------- ----------- ----- -------
00001    01-04-2015 CS001       2     10000
00002    08-04-2015 CS001       3     7000
00003    14-04-2015 CS004       1     15000

Di bawah ini adalah contoh SQL query menggunakan Union pada kedua tabel diatas.

SQL> SELECT customer.customer_id, customer.customer_name, orders.order_id, orders.order_date, orders.qty, orders.amount
  2  FROM customer
  3  LEFT JOIN orders
  4  ON customer.customer_id = orders.customer_id
  5  UNION
  6  SELECT customer.customer_id, customer.customer_name, orders.order_id, orders.order_date, orders.qty, orders.amount
  7  FROM customer
  8  RIGHT JOIN orders
  9  ON customer.customer_id = orders.customer_id;

CUSTOMER_ID CUSTOMER_NAME      ORDER_ID ORDER_DATE QTY   AMOUNT
----------- ------------------ -------- ---------- ----- -------
CS001       Nursalim           00001    01-04-2015     2   10000
CS001       Nursalim           00002    08-04-2015     3    7000
CS002       Nani Indriyani
CS003       Naura Krasiva
CS004       Ahmad Fatoni       00003    14-04-2015     1   15000
CS005       Sri Rahayu

6 rows selected.

Share on Google Plus

About Unknown

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.