DB2 - SQL LEFT OUTER JOIN

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

Syntax:

SELECT column_name(s)
  FROM table1
  LEFT 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 select all customers, and any orders they might have placed.

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

Explanation:

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

Result:

Have you noticed the LEFT outer and RIGHT 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!