TSO-ISPF JCL COBOL VSAM DB2 CICS Tools Articles Job Portal Forum Quiz Interview Q&A

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:

OrderidCustomernumberOrderdate
1001102020-09-08
1002992020-09-01
1003172020-08-25
1004762020-09-19
1005442020-09-25


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

CustomernumberCustomernameCountry
76JackAmerica
17JancyGermany
20CarmenRussia
10RobertIndia
99BrianChina

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:
CustomernameOrderid
Brian1002
Carmennull
Jack1004
Jancy1003
Robert1001

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!

Are you looking for Job Change? Job Portal