JOIN Clauses compared to WHERE Clauses

Question

I get two different answers when I run the two following queries. I would really like to know why. Is it the LEFT JOIN? Is it the order of operations? Could this occur in other JOIN types?

First Query

SELECT
First_Name
, E.Employee_ID
, I.Employee_REF_ID
FROM Employee AS E
LEFT JOIN Incentives AS I
ON (E.Employee_ID = I.Employee_REF_ID)  AND (I.Employee_Ref_Id IS NOT NULL);

Output

1.  First_Name  Employee_ID Employee_REF_ID
2.  John    1   1
3.  John    1   1
4.  Michael 2   2
5.  Michael 2   2
6.  Roy 3   3
7.  Tom 4   NULL
8.  Jerry   5   NULL
9.  NULL    6   NULL
10. TestName1   7   NULL
11. Lname%  8   NULL

Second Query

SELECT
First_Name
, E.Employee_ID
, I.Employee_REF_ID
   FROM Employee AS E
LEFT JOIN Incentives AS I
ON (E.Employee_ID = I.Employee_REF_ID)  
WHERE I.Employee_Ref_Id IS NOT NULL;

Output

1.  First_Name  Employee_ID Employee_REF_ID
2.  John    1   1
3.  Michael 2   2
4.  Roy 3   3
5.  John    1   1
6.  Michael 2   2

Original code from http://narendra86.blogspot.com/2013/10/top-80-sql-query-interview-questions.html

Answer 1

Take this as an example:

declare @table1 table (emp_id int, name varchar(10))
declare @table2 table (emp_ref_id int, ref_name varchar(10))

insert into @table1 values (1, 'emp1'), (2, 'emp2'), (3, 'emp3')
insert into @table2 values (1, 'empref1'), (2, 'empref2')

This query:

select *
from @table1 t1
left join @table2 t2 on t1.emp_id = t2.emp_ref_id and t2.emp_ref_id is not null

returns:

emp_id  name    emp_ref_id  ref_name
1       emp1    1           empref1
2       emp2    2           empref2
3       emp3    NULL        NULL

But this query:

select *
from @table1 t1
left join @table2 t2 on t1.emp_id = t2.emp_ref_id
where t2.emp_ref_id is not null

returns:

emp_id  name    emp_ref_id  ref_name
1       emp1    1           empref1
2       emp2    2           empref2

The difference is that on the first query the t2.emp_ref_id is not null condition doesn't have an effect because it's a left join so even if the right table doesn't have a matching row, those columns will be returned as null. On the 2nd query the condition t2.emp_ref_id is not null is checked against what's returned from the query so it removes the rows with null emp_ref_id.

Another example:

declare @table1 table (emp_id int, name varchar(10))
declare @table2 table (emp_ref_id int, ref_name varchar(10), col3 varchar)

insert into @table1 values (1, 'emp1'), (2, 'emp2'), (3, 'emp3')
insert into @table2 values (1, 'empref1', 'a'), (2, 'empref2', null)

select *
from @table1 t1
left join @table2 t2 on t1.emp_id = t2.emp_ref_id and t2.col3 is not null

will return:

emp_id  name    emp_ref_id  ref_name    col3
1       emp1    1           empref1     a
2       emp2    NULL        NULL        NULL
3       emp3    NULL        NULL        NULL

If you use this query:

select *
from @table1 t1
left join @table2 t2 on t1.emp_id = t2.emp_ref_id 

It returns:

emp_id  name    emp_ref_id  ref_name    col3
1       emp1    1           empref1     a
2       emp2    2           empref2     NULL
3       emp3    NULL        NULL        NULL

As you can see, since it's a left join, when you have the condition and t2.col3 is not null it just means the left join can't find a row to match with from @table2, but will still return the row from table2, with null values.

Answer 2

The JOIN allows you to link the second table to the first based on the condition you set.

In your first query, LEFT JOIN will give you all columns from joined table NULL if there's no matching.

In your second query, your WHERE clause has further removed those non-matching records so there's no records with NULL.

Would give you better explanation if you can provide sample data on your tables.

Answer 3

Your result is different because of the way Left join is evaluated - having a condition in ON clause can give a different result than having the equivalent condition in the WHERE clause (when you have the ON clause - we get NULL in the right side when there is no match but in WHERE clause the rows are filtered out ).

Check this stackoverflow answer for more details - SQL join: where clause vs. on clause

Answer 4

You should use an ORDER BY clause to force the order that you want. For example:

SELECT
First_Name
, E.Employee_ID
, I.Employee_REF_ID
FROM Employee AS E
LEFT JOIN Incentives AS I
ON (E.Employee_ID = I.Employee_REF_ID)  AND (I.Employee_Ref_Id IS NOT NULL)
ORDER BY First_Name ASC;