DB2 - SQL FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records. FULL OUTER JOIN and FULL JOIN are the same.

FULL OUTER JOIN can potentially return very large result-sets. Because, FULL OUTER JOIN returns all matching records from both tables whether the other table matches or not.

Syntax:

SELECT column_name(s)
  FROM table1
  FULL OUTER JOIN table2
  ON table1.column_name = table2.column_name
  WHERE condition;

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 selects all customers, and all orders:

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

Result:

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