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 wanted 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 error in the data!
1 thought on “WHERE Clause Conditions with OUTER Joins”