WHERE Clause Conditions with OUTER Joins

Issues that arise out of using Where clause in Outer Joins and how to avoid that.

One common mistake that people make while writing Outer Joins is incorrectly specifying a condition in the query. It’s such an innocent mistake that unless you have an idea of the expected result you won’t even realize that you made a mistake.

human computer blaming each other

Let me explain using the following two tables:

Table Name: Emp_Dept

 EmpID EmpNameDept
1SuriOps
2DivyaHR
3MayaHR
4RahulIT

Table Name: Emp_Loc

 EmpID StateCity
1UPKanpur
3RajasthanJaipur
4TelanganaHyderabad

Let’s assume that you are doing a Left Join (aka Left Outer Join) on tables Emp_Dept & Emp_Loc.

Select a.EmpId, a.EmpName, a.Dept, b.City
 From Emp_Dept a Left Join Emp_Loc b
 On a.EmpId=b.EmpId

Result:

 EmpID EmpNameDeptCity
1SuriOpsKanpur
2DivyaHR 
3MayaHRJaipur
4RahulITHyderabad

Now if you include a condition in your query using a WHERE clause that applies to the secondary table (i.e. the table on the RIGHT of a LEFT JOIN), the query will not act like an OUTER join, because you’ve limited the results with conditions on both tables. You’ve essentially overridden the OUTER JOIN by limiting the results to only records that exist in the secondary table, thus making it behave like an INNER join

Confused?

Let’s understand this by applying a WHERE clause condition on the CITY column of Emp_Loc – which is on the RIGHT of a LEFT JOIN:

Select a.EmpId, a.EmpName, a.Dept, b.City
 From Emp_Dept a Left Join Emp_Loc b
 On a.EmpId=b.EmpId
 Where b.City='Jaipur'

In this case, we’ll get only the rows for which the given condition is true – making the query behave like an INNER join:

See also  7 Tips to Optimize SQL Queries
 EmpID EmpNameDeptCity
3MayaHRJaipur

If this is not how you want your output to be, you should use the condition in the Join clause itself, as shown below:

Select a.EmpId, a.EmpName, a.Dept, b.City
 From Emp_Dept a Left Join Emp_Loc b
 On a.EmpId=b.EmpId
 And b.City='Jaipur'

The result of the above query will be:

 EmpID EmpNameDeptCity
1SuriOps 
2DivyaHR 
3MayaHRJaipur
4RahulIT 

So, when you specify the condition using a Where clause, it applies the filter on the result of the entire query, whereas when you apply the condition in the Join clause, it applies the filter only on the Join.

A small concept, which if ignored, can inject huge errors in the data!

I write on various topics such as productivity, tech, books, personal finance, and more. To stay updated, choose either (or both) of the options below:

Get Real-Time Alerts

Stay in the loop with real-time alerts. Join our WhatsApp channel for instant notifications on new articles, captivating book snippets, noteworthy web finds, and more. Experience it as it happens.

Monthly Email Digest

Prefer a monthly roundup? Subscribe to the Email Newsletter to receive a neatly organized digest at the end of each month. Explore everything I wrote, handpicked web gems, and more – delivered to your inbox in a well-packaged newsletter format.


Photo Credit:

  1. Featured Image: Photo by Kevin Ku on Unsplash

One comment

  1. […] remember that use of WHERE with OUTER JOIN can lead to the incorrect results. Check this article for […]

Leave a Reply

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