DB2 - SQL RIGHT OUTER JOIN

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

Syntax:

SELECT column_name(s)
  FROM table1
  RIGHT JOIN table2
   ON table1.column_name = table2.column_name;

DB2 Database:

Let's look at the "Orders" table data below:

Let's look at the "Customers" table data below:

Notice that the "Customernumber" column in the "Orders" table refers to the "Customernumber" in the "Customers" table. The relationship between the two tables above is the "Customernumber" column.

Let us see how to create SQL statement that will return all Customername, and any orders they might have placed:

SELECT Customers.Customername, Orders.Orderid
  FROM Orders
  LEFT JOIN Customers ON Orders.Customernumber = Customers.Customernumber;
  ORDER BY Customers.Customername;

Explanation:

The RIGHT JOIN keyword returns all records from the right table (Customers), even if there are no matches in the left table (Orders).

Result:

Have you noticed the RIGHT outer and LEFT outer join result? The result is same. Do you know why? because we just interchanged the table in SQL statement.

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!