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.
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; |
Let's look at the "Orders" table data below:
Orderid | Customernumber | Orderdate |
---|---|---|
1001 | 10 | 2020-09-08 |
1002 | 99 | 2020-09-01 |
1003 | 17 | 2020-08-25 |
1004 | 76 | 2020-09-19 |
1005 | 44 | 2020-09-25 |
Let's look at the "Customers" table data below:
Customernumber | Customername | Country |
---|---|---|
76 | Jack | America |
17 | Jancy | Germany |
20 | Carmen | Russia |
10 | Robert | India |
99 | Brian | China |
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; |
The RIGHT JOIN keyword returns all records from the right table (Customers), even if there are no matches in the left table (Orders).
Customername | Orderid |
---|---|
Brian | 1002 |
Carmen | null |
Jack | 1004 |
Jancy | 1003 |
Robert | 1001 |
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!