I have a SQL query which does not perform as expected and returns incomplete results. In the query below, valid t1 rows are not included in the query results if there are no matching ids in t2.
Select t1.id, t2.code
From table1 t1
left join t2 on t1.id = t2.id
where t2.code != 'value'
When removing the Where clause, the results return valid t1.id data, with nulls in the t2 columns
Select t1.id, t2.code
From table1 t1
left join t2 on t1.id = t2.id
CORRECTLY RETURNS
t1.id | t2.code
1234 | null
I can get around the issue by adding IIF to the Where clause, however this method is not a viable solution in my reporting tools:
Select t1.id, t2.code
From table1 t1
left join t2 on t1.id = t2.id
where iif(t2.code is null, '', t2.code) != 'value'
CORRECTLY RETURNS
t1.id | t2.code
1234 | null
Why does including t2.code != 'value' remove the t1.id = 1234 row from the results? The t2 side of the results are null, and thus not equal to 'value', so shouldn't the row be included in the results?