SQL – Right Outer Join

Main_right outer join

admin Oracle, SQL Joins , ,

Hi Oracle folks,
After Left Outer Join, it is time to learn about Right Outer Join.

What is Right Outer Join?
Right Outer Join or Right Join helps to fetch/select the data (rows and columns) from multiple tables. It returns all the rows from the RIGHT table and only matching rows from LEFT table.
In simple words I can say, Right Join works just opposite to the Left Join.
Inner Join

In above picture, highlighted area is representing the output of Right Outer Join.

Let’s have look at the syntax which is pretty much similar to the Left Outer Join.
Just replace Left with Right.

Syntax:

SELECT a.col1, a.col2, b.col1, b.col2 FROM
left_table1 a RIGHT OUTER JOIN right_table2 b
ON
<join condition> [WHERE…];

Same as Left Outer Join, we can replace RIGHT OUTER JOIN with RIGHT JOIN. There will be no difference in the output.

We will use the EMP table and DEPARTMENTS table to understand it with the same query but with little changes.

EMPLOYEES_IDFIRST_NAMEDEPARTMENT_ID
113Luis100
119Karen30
178Kimberely(null)
102Lex90
202Pat20
204Hermann70
206William110

This time we are not going to use DEPARTMENTS table but I have created a new table DEPT with two column of original table and a few rows.

DEPARTMENT_IDDEPARTMENT_NAME
10Administration
20Marketing
30Purchasing
40Human Resources
50Shipping
60IT
70Public Relations
80Sales
90Executive
100Finance
110Accounting
120Treasury

You can see in the below query that I have replaced the keyword LEFT OUTER JOIN with RIGHT OUTER JOIN and added DEPT table instead of DEPARTMENT table.

When we run above code, Oracle will generate only matching records from EMP table and all the records from DEPT table. Where DEPT table has data and EMP does not have, Oracle Engine shows “(null)” but when we take export in Excel it shows blank.

DEPARTMENT_IDEMPLOYEES_IDFIRST_NAMEDEPARTMENT_NAME
100113LuisFinance
30119KarenPurchasing
90102LexExecutive
20202PatMarketing
70204HermannPublic Relations
110206WilliamAccounting
(null)(null)(null)Shipping
(null)(null)(null)Human Resources
(null)(null)(null)Administration
(null)(null)(null)Treasury
(null)(null)(null)IT
(null)(null)(null)Sales

Exercise:

Replace the tables’ position again in the above query without changing the join type and see the results.

If you have any questions/suggestions, please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

NULLIF

Oracle SQL_Null Related functions-3_NULLIF

Hi Aliens, Today, we will talk about another Null Related Function, NULLIF. You can read my notes on NVL, NVL2 […]

WHERE

SQL – WHERE clause

Hi DBgeek Army, Welcome back to the world of Oracle! Where clause helps to select desired rows or helps to […]

comments

Comment in SQL and PL-SQL

Hi DBgeek Army, Welcome back to the world of Oracle! Comments are very important aspects of our code. No matter […]

Leave a Reply

Your email address will not be published. Required fields are marked *