Hi Oracle folks,
In this blog, we will talk about the OUTER JOIN.
What is an OUTER JOIN?
Outer join is little advance to the Inner Join. In inner join, Oracle generates only matching or common rows out of two or more tables while in Outer join, Oracle generates matching records as well as non-matching records.
As I mentioned in the 1st blog of this series Introduction of Joins that Outer Join is further divided into 3 types of Joins. In this blog, we will talk about LEFT OUTER JOIN only.
LEFT OUTER JOIN:
Left outer Join or Left Join helps to fetch or select the data (rows and columns) from multiple tables. It returns all the rows from LEFT table and only matching rows from RIGHT table.
In above picture, highlighted area is representing the output of Left Outer Join.
Let’s have look at the syntax so that I can explain you Left and Right table.
Syntax:
left_table1 a LEFT OUTER JOIN right_table2 b
ON
<join condition> [WHERE…];
You can see in the syntax that LEFT TABLE is a table mentioned in the left side of LEFT OUTER JOIN keyword and RIGHT TABLE is a table mentioned in the right side of LEFT OUTER JOIN keyword.
We can replace LEFT OUTER JOIN with LEFT JOIN. There will be no difference in the output.
We can use the table EMP table and DEPARTMENTS table to understand it.
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 |
In EMP table, we have 7 records. Out of which, one record is having DEPARTMENT_ID as NULL. That’s the reason it was not matching with DEPARTMENTS table and we had only 6 records in case of Inner Join.
I am going to replace INNER JOIN with LEFT OUTER JOIN and run that query. Rest everything is same as earlier.
SELECT
a.*, --all columns from EMP table
b.department_name --department_name from DEPARTMENTS table
FROM
emp a LEFT OUTER JOIN departments b
ON a.department_id = b.department_id;
So I have mentioned EMP table in the left side and DEPARTMENTS table in the right side of LEFT OUTER JOIN keyword. As per the definition of LEFT OUTER JOIN, We should have all the records from EMP table (including that record where DEPARTMENT_ID is null) and only matching records from DEPARTMENTS table.
When we run above code, Oracle will generate output as shown below.
EMPLOYEES_ID | FIRST_NAME | DEPARTMENT_ID | DEPARTMENT_NAME |
---|---|---|---|
202 | Pat | 20 | Marketing |
119 | Karen | 30 | Purchasing |
204 | Hermann | 70 | Public Relations |
102 | Lex | 90 | Executive |
113 | Luis | 100 | Finance |
206 | William | 110 | Accounting |
178 | Kimberely | (null) | (null) |
You can see, now we have all the records from EMP table including DEPARTMENT_ID null and from DEPARTMENTS table we only have matching reocrds. That’s the definition of Left Outer Join.
Exercise:
Replace the tables’ position in the SELECT query 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