WHERE Clause Conditions with OUTER Joins

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!

Photo Credit:

1. Featured Image: Photo by Kevin Ku on Unsplash