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.

 
SELECT
    *
FROM
    emp;
EMPLOYEES_IDFIRST_NAMEDEPARTMENT_ID
113Luis100
119Karen30
178Kimberely(null)
102Lex90
202Pat20
204Hermann70
206William110

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_IDFIRST_NAMEDEPARTMENT_IDDEPARTMENT_NAME
202Pat20Marketing
119Karen30Purchasing
204Hermann70Public Relations
102Lex90Executive
113Luis100Finance
206William110Accounting
178Kimberely(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..

DUAL TABLE

Oracle_DUAL table

Hey geeks, DUAL table is a very important table available in the Oracle database. It gets created automatically during the […]

Concatenation Operator

Oracle_Concatenation Operator

Hey geeks, This is another chapter of Operators. Concatenation Operator helps to concatenate two or more strings or columns. The […]

Parentheses

Parentheses in SQL

Hey time travellers, Though Parentheses are used pretty much everywhere in SQL but in this blog, we will talk about […]

Leave a Reply

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