SQL – Left Outer Join

Main_left outer join

admin Oracle, SQL Joins , ,

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.

Inner Join

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:

SELECT a.col1, a.col2, b.col1, b.col2 FROM
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.

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.

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

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 *