Another type of join is called a
SQL RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand
table specified in the ON condition and only those rows from
the other table where the joined fields are equal (join condition is met).
Syntax
The syntax for the SQL RIGHT
OUTER JOIN is:
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
Visual Illustration
In this visual diagram, the SQL
RIGHT OUTER JOIN returns the shaded area:
The SQL RIGHT OUTER JOIN would
return the all records from table2
and only those records from table1
that intersect with table2.
Example
Here is an example of a SQL RIGHT
OUTER JOIN:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
This RIGHT OUTER JOIN example would
return all rows from the orders table and only those rows from the suppliers
table where the joined fields are equal.
If a supplier_id value in the
orders table does not exist in the suppliers table, all fields in the suppliers
table will display as <null>
in the result set.
Let's look at some data to explain
how RIGHT OUTER JOINS work:
We have a table called suppliers with two fields
(supplier_id and supplier_name). It contains the following data:
supplier_id
|
supplier_name
|
10000
|
Apple
|
10001
|
Google
|
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
|
2013/08/12
|
500126
|
10001
|
2013/08/13
|
500127
|
10002
|
2013/08/14
|
If we run the SQL statement (that
contains a RIGHT OUTER JOIN) below:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
Our result set would look like
this:
order_id
|
order_date
|
supplier_name
|
500125
|
2013/08/12
|
Apple
|
500126
|
2013/08/13
|
Google
|
500127
|
2013/08/14
|
<null>
|
The row for 500127 (order_id) would be
included because a RIGHT OUTER JOIN was used. However, you will notice that the
supplier_name field for that record contains a <null> value.
Old Syntax
As a final note, it is worth
mentioning that the RIGHT OUTER JOIN example above could be rewritten using the
older implicit syntax that utilizes the outer join operator (+) as follows (but
we still recommend using the RIGHT OUTER JOIN keyword syntax):
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers, orders
WHERE suppliers.supplier_id(+) = orders.supplier_id;
9ce
ReplyDelete