user2953027 user2953027 - 1 month ago 6
SQL Question

Is this incredibly intelligent or absolutely useless "where (table.field = NULL or null is null)"?

I have found that snippet in a query many times, for almost every table in the FROM. I can't find any sense to it. Can anyone help, please? My gut tells me it's useless, but maybe it's something so clever that's way over my head. Example:

Select a.field1, b.field2, c.field3
from table1 a
inner join table2 b on (a.field1 = b.id)
left join table 3 c on (b.field2 = c.id)
where a.field 3 = "VALUE"
and ( a.field1 = NULL or NULL IS NULL)
and ( b.field2 = NULL or NULL IS NULL)

Answer

NULL IS NULL is always true, so combining it with any other condition with an or operator will also result in true, making the last two conditions useless. This query could be rewritten without them:

Select a.field1, b.field2, c.field3
from table1 a
inner join table2 b on (a.field1 = b.id)
left join table 3 c on (b.field2 = c.id)
where a.field 3 = "VALUE"
Comments