Full Join Pada Oracle SQL

Untuk menggabungkan dua tabel menggunakan  Full Join, Anda dapat menggunakan keyword FULL JOIN pada query SQL.

Sintak Dasar

Di bawah ini adalah sintak dasar join query menggunakan FULL JOIN di Oracle SQL:

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

Contoh

Asumsikan Anda telah membuat 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

Untuk men-join kedua tabel diatas menggunakan FULL JOIN, Anda dapat menggunakan query di bawah ini:

SQL> SELECT customer.customer_id, customer.customer_name, orders.order_id, orders.order_date, orders.qty, orders.amount
  2  FROM customer
  3  FULL JOIN orders
  4  ON customer.customer_id = orders.customer_id;

CUSTOMER_ID CUSTOMER_NAME    ORDER_ID ORDER_DATE QTY     AMOUNT
----------- ---------------- -------- ---------- ---- ----------
CS001       Nursalim         00002    08-04-2015    3       7000
CS001       Nursalim         00001    01-04-2015    2      10000
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.