SQL working strange

Question

When I execute this SQL code:

select id, opis, vidrabota, tipprov, hitnost, valuta, drzava, zbirnaprov, tip_zbirprov, kanal
from dev_1450autoebanktip
where opis is null or Opis like case when isnull('','') = '' then Opis else '%' + '' + '%' end
and VidRabota = case when isnull('','') = '' then VidRabota else '' end
and TipProv = case when isnull(0,0) = 0 then TipProv else 0 end
and Valuta = case when isnull('','') = '' then Valuta else '' end
and drzava is null or Drzava = case when isnull('','') = '' then Drzava else '' end

I get this set of results:

But when I add one more condition (last row):

select id, opis, vidrabota, tipprov, hitnost, valuta, drzava, zbirnaprov, tip_zbirprov, kanal
from dev_1450autoebanktip
where opis is null or Opis like case when isnull('','') = '' then Opis else '%' + '' + '%' end
and VidRabota = case when isnull('','') = '' then VidRabota else '' end
and TipProv = case when isnull(0,0) = 0 then TipProv else 0 end
and Valuta = case when isnull('','') = '' then Valuta else '' end
and drzava is null or Drzava = case when isnull('','') = '' then Drzava else '' end
and KANAL = case when isnull(0,0) = 0 then KANAL else 0 end

I am losing one row in the result. What is causing this change?

Answer 1

Kanal is NULL in the last row.

and KANAL = case when isnull(0,0) = 0 then KANAL else 0 end

boils down to

and KANAL = KANAL

But this is not true for NULL, because NULL is the unknown value. When comparing null with null the result is neither true nor false, but unknown. Thus the added criteria dismisses the last record.

There is one thing I'd like to add: Use parentheses when mixing AND and OR. For instance

a = b or a = c and d = e

means

a = b or (a = c and d = e)

because AND has precedence over OR and you may want the expression to mean

(a = b or a = c) and d = e

Use parentheses in order to avoid any mistakes.