-1

I have never seen this before and found it interesting so, I wanted to share it with you inquiring why the different behavior. (See that there is a LEFT JOIN involved.)

In my database, the following returns only one record:

select * 
from 
   TableA a 
   left join TableB b on a.MyColumnID = b.MyColumnId 
where 1=1 
   and b.IsActive = 1

On the other hand, this other script returns two records. the first one containing NULL in the IsActive field:

select * 
from 
TableA a 
left join TableB b on a.MyColumnId = b.MyColumnId and b.IsActive = 1
where 1=1

I just found out that it does not return the same but, I do not know why. Anyone may know?

frankztein
  • 153
  • 1
  • 1
  • 6
  • `JOIN` is for join conditions and `WHERE` for filtering, that it will be the same results for `INNER JOIN`. Use each on for the right purpose. – Ilyes Oct 22 '18 at 17:41
  • Hello, Sami and Tab. I appreciate very much your answers. Though the other question is similar, it is a different scenario since my question is specific for left joins. The other asks only on inner joins – frankztein Oct 22 '18 at 18:21

3 Answers3

1

A left join follows a simple rule. It keeps all rows in the first table, regardless of what the on clause evaluates to. It keeps matching rows in the second table when the on clause evaluates to true. In other cases, the corresponding columns for the second table for each row in the first table are NULL.

Your first query is:

select * 
from TableA a left join
     TableB b
     on a.MyColumnID = b.MyColumnId 
where 1 = 1 and b.IsActive = 1;

The where clause is interpreted after the join. So, if a has a row that does not match b, then b.IsActive is NULL. NULL fails the comparison so the row is filtered out. This is usually shortened to something along the lines of: "the where clause changes the left join to an inner join."

Of course, if the condition is in the on clause, then this does not apply.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

The WHERE in the first query turns the LEFT JOIN into an implicit INNER JOIN. If no row is returned, the cause cannot return true, and thus are filtered out.

The WHERE 1=1 in the both queries, however, is just a burden on the optimiser and should be removed.

Thom A
  • 88,727
  • 11
  • 45
  • 75
0

It is returning different results because you are doing a left join.

A left join by itself will return any data from first table, and if any data exists in second table, it will return the data for second table, but it can be blank/no records in second table.

In this one the where clause with b.IsActive = 1 is forcing a value to appear in TableB no matter what the value is for IsActive flag (forcing an Inner Join)

select * 
from 
   TableA a 
   left join TableB b on a.MyColumnID = b.MyColumnId 
where 1=1 
   and b.IsActive = 1

For this one, since you are adding the b.IsActive to the join, it does not require a value to appear in TableB, and will return all values in TableA regardless, but only return records where TableB.ISActive = 1 

select * 
from 
TableA a 
left join TableB b on a.MyColumnId = b.MyColumnId and b.IsActive = 1
where 1=1
Brad
  • 3,454
  • 3
  • 27
  • 50