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.

Let me explain using the following two tables:
Table Name: Emp_Dept | ||
EmpID | EmpName | Dept |
1 | Suri | Ops |
2 | Divya | HR |
3 | Maya | HR |
4 | Rahul | IT |
Table Name: Emp_Loc | ||
EmpID | State | City |
1 | UP | Kanpur |
3 | Rajasthan | Jaipur |
4 | Telangana | Hyderabad |
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 | EmpName | Dept | City |
1 | Suri | Ops | Kanpur |
2 | Divya | HR | |
3 | Maya | HR | Jaipur |
4 | Rahul | IT | Hyderabad |
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:
EmpID | EmpName | Dept | City |
3 | Maya | HR | Jaipur |
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 | EmpName | Dept | City |
1 | Suri | Ops | |
2 | Divya | HR | |
3 | Maya | HR | Jaipur |
4 | Rahul | IT |
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!
Thanks for reading. Please leave a comment with your thoughts. And share this blog with whoever you think might like it.
Go Deeper on Data, Finance, and Productivity
If you enjoyed this article, you’ll love my newsletter, Compound Knowledge, where I share actionable ideas and the best resources I’ve found to help you uplevel your skills in data, finance, and productivity. It’s concise, valuable, and free.
Join the inner circle of readers and get insights directly in your inbox.
Photo Credit:
[…] remember that use of WHERE with OUTER JOIN can lead to the incorrect results. Check this article for […]