in Data

WHERE Clause Conditions with OUTER Joins

SQL ErrorOne 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!

Share This:

Write a Comment

Comment