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_ID FIRST_NAME DEPARTMENT_ID
113 Luis 100
119 Karen 30
178 Kimberely (null)
102 Lex 90
202 Pat 20
204 Hermann 70
206 William 110

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_ID DEPARTMENT_NAME
10 Administration
20 Marketing
30 Purchasing
40 Human Resources
50 Shipping
60 IT
70 Public Relations
80 Sales
90 Executive
100 Finance
110 Accounting
120 Treasury

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_ID EMPLOYEES_ID FIRST_NAME DEPARTMENT_NAME
100 113 Luis Finance
30 119 Karen Purchasing
90 102 Lex Executive
20 202 Pat Marketing
70 204 Hermann Public Relations
110 206 William Accounting
(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..

merge

SQL – MERGE Statement

Hi Devs, As we have already learned INSERT INTO and UPDATE statement, our next very important statement is MERGE. Merge […]

update2

SQL – UPDATE Statement part-2

Hi Geeks, In previous blog on UPDATE statement, we learnt, how can we update a column value with a string […]

update

SQL – UPDATE Statement part-1

Hi Geeks, We already have a separate blog on INSERT INTO statement. So today, we will talk about UPDATE statement […]

Leave a Reply

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