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.
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:
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.
SELECT
*
FROM
emp;
| 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.
SELECT
*
FROM
dept;
| 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.
SELECT
a.*, --all columns from EMP table
b.department_name --department_name from DEPARTMENTS table
FROM
emp a RIGHT OUTER JOIN dept b
ON a.department_id = b.department_id;
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
