|
A right outer join is a method of combining tables.
The result includes unmatched rows from only the table that is specified after the RIGHT OUTER JOIN clause.
If you are joining two tables and want the result set to include unmatched rows from only one table, use a LEFT OUTER JOIN clause or a RIGHT OUTER JOIN clause.
The matching is based on the join condition.
The clause RIGHT OUTER JOIN includes rows from the table that is specified after RIGHT OUTER JOIN that have no matching values in the table that is specified
before RIGHT OUTER JOIN.
As in an inner join, the join condition can be any simple or compound search condition that does not contain a subquery reference.
Example:
The following example uses the tables in Sample data for joins .
To include rows from the PRODUCTS table that have no corresponding rows in the PARTS table, execute this query:
SELECT PART
, SUPPLIER
, PRODUCTS.PROD#
, PRODUCT
, PRICE
FROM PARTS RIGHT OUTER JOIN PRODUCTS
ON PARTS.PROD# = PRODUCTS.PROD#
AND PRODUCTS.PRICE > 10.00;
The result table looks similar to the following output:
PART SUPPLIER PROD# PRODUCT PRICE
======= ============ ===== ========== =====
WIRE ACWF 10 GENERATOR 45.75
MAGNETS BATEMAN 10 GENERATOR 45.75
BLADES ACE_STEEL 205 SAW 18.90
---------- ------------ 30 RELAY 7.55
---------- ------------ 505 SCREWDRIVER 3.70
A row from the PARTS table is in the result table only if its product number matches the product number of a row in the PRODUCTS table and the price is
greater than 10.00 for that row.
Because the PRODUCTS table can have rows with nonmatching product numbers in the result table, and the PRICE column is in the PRODUCTS table, rows in which
PRICE is less than or equal to 10.00 are included in the result.
The PARTS columns contain null values for these rows in the result table.
© Copyright IBM Corp.
|
