The PL/SQL FULL JOIN returns all rows from left table and right table even if the condition is not fulfilled.
Phones table
| PHONE_ID | PHONE_NAME |
|---|---|
| 1 | Galaxy Note Edge |
| 2 | N910 Galaxy Note 4 |
| 3 | Galaxy S5 Duos 4G |
| 4 | iPhone 5S |
| 5 | iPhone 6 Plus |
| 6 | 930 Lumia |
| 7 | 830 Lumia |
Phone_orders table
| ORDER_ID | PHONE_ID |
|---|---|
| 1 | 3 |
| 2 | 3 |
| 3 | 4 |
| 4 | 6 |
Full Join example
SELECT p.phone_id, p.phone_name, o.order_id FROM phones p FULL JOIN phone_orders o ON p.phone_id=o.phone_id ORDER BY p.phone_id, o.order_id;
Output
| PHONE_ID | PHONE_NAME | ORDER_ID |
|---|---|---|
| 1 | Galaxy Note Edge | (null) |
| 2 | N910 Galaxy Note 4 | (null) |
| 3 | Galaxy S5 Duos 4G | 1 |
| 3 | Galaxy S5 Duos 4G | 2 |
| 4 | iPhone 5S | 3 |
| 5 | iPhone 6 Plus | (null) |
| 6 | 930 Lumia | 4 |
| 7 | 830 Lumia | (null) |