DB2 - Right Outer Join Statement




DB2 Right Outer Join Statement

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.

RIGHT OUTR JOIN will retrieves matched rows from the tables which are provided before RIGHT OUTR JOIN keyword and all rows from the tables provide after the keyword. Let us say, A and B table are joining. A has the matching row for a specific value in matching columns and B also should have the row with the matching value. Then only the row will appear in result table.

Let us say, A and B table are joining. A has no matching row for a specific value in matching columns. But B has row with the matching value. Still the row is joined in OUTER JOIN, but the columns of A will be filled up with NULL values in result table.

Syntax

SELECT A-columns-list,
       B-columns-list
  FROM table-A RIGHT OUTER JOIN table-B
    ON joining-column(s)
 WHERE condition

What is DB2 Right Outer Join Statement ?

The RIGHT JOIN clause is a reversed version of the LEFT JOIN clause. The RIGHT JOIN clause allows you to query data from two or more tables. Suppose, you have two tables named T1 and T2, which are called the left table and the right table respectively.

The RIGHT JOIN clause selects data starting from the right table (T2). It compares each row in the right table (T2) with every row in the left table (T1). If two rows satisfy the join condition, the RIGHT JOIN clause combines columns of these rows into a new row and includes this new row in the result. In case a row in the right table does not have a matching row in the left table, the RIGHT JOIN clause still combines the columns of the row in the right table with the columns of the row in the left table. However, the columns in the left table will have NULL values.

In other words, the RIGHT JOIN clause returns all rows from the right table (T2) and matching rows or NULL values from the left table (T1).

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 -

Example

SELECT PART, SUPPLIER, PRODUCTS.PROD#, PRODUCT, PRICE
  FROM PARTS RIGHT OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#
       AND PRODUCTS.PRICE>10.00;

Result

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