Outer Join
The second type of join is called an outer join that returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).
For example,
select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);
This SQL statement would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.
The (+) after the orders.supplier_id field indicates that, if a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as <null> in the result set.
The above SQL statement could also be written as follows:
select *
from suppliers, orders
where orders.supplier_id(+) = suppliers.supplier_id
Let's look at some data to explain how outer joins work:
We have a table called suppliers with two fields (supplier_id and name).
It contains the following data:
| supplier_id |
supplier_name |
| 10000 |
IBM |
| 10001 |
Hewlett Packard |
| 10002 |
Microsoft |
| 10003 |
Nvidia |
We have a second table called orders with three fields (order_id, supplier_id, and order_date).
It contains the following data:
| order_id |
supplier_id |
order_date |
| 500125 |
10000 |
2003/05/12 |
| 500126 |
10001 |
2003/05/13 |
If we ran the SQL statement below:
select *
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);
Our result set would look like this:
| supplier_id |
supplier_name |
order_date |
| 10000 |
IBM |
2003/05/12 |
| 10001 |
Hewlett Packard |
2003/05/13 |
| 10002 |
Microsoft |
<null> |
| 10003 |
Nvidia |
<null> |
The rows for Microsoft and Nvidia would be included because an outer join was used. However, you will notice that the order_date field for those records contains a <null> value.
See also insert into Oracle
See also group by sql
See also select into sql
|