Testing for inequality in T-SQL

Question

I've just come across this in a WHERE clause:

AND NOT (t.id = @id)

How does this compare with:

AND t.id != @id

Or with:

AND t.id <> @id

I'd always write the latter myself, but clearly someone else thinks differently. Is one going to perform any better than the other? I know that using <> or != is going to bust any hopes for using an index that I might have had, but surely the first approach above will suffer the same problem?

Answer 1

Note that the != operator is not standard SQL. If you want your code to be portable (that is, if you care), use <> instead.

Answer 2

Logic Hazard On Equality to Null To Be Considered

The equality operator generates an unknown value when there is a null and the unknown value is treated a false. Not (unknown) is still unknown.

In the example below I'll ask if a couple (a1, b1) is equal to (a2, b2). Note that each column has 3 values: 0, 1 and NULL.

DECLARE @t table (a1 bit, a2 bit, b1 bit, b2 bit)

Insert into @t (a1 , a2, b1, b2) 
values( 0 , 0 , 0 , NULL )

select 
a1,a2,b1,b2,
case when (
    (a1=a2 or (a1 is null and a2 is null))
and (b1=b2 or (b1 is null and b2 is null))
)
then 
'Equal'
end,
case when not (
    (a1=a2 or (a1 is null and a2 is null))
and (b1=b2 or (b1 is null and b2 is null))
)
then 
'Not Equal'
end,
case when (
    (a1<>a2 or (a1 is null and a2 is not null) or (a1 is not null and a2 is null))
or (b1<>b2 or (b1 is null and b2 is not null) or (b1 is not null and b2 is null))
)
then 
'Different'
end
from @t

Note that here, the results we expect are:

• Equal to be null
• Not equal to be not equal
• Different to be different

But instead, we get another result

• Equal is null - what we expected.
• Not Equal is null ???
• Different is different - what we expected.

Answer 3

There will be no performance hit, both statements are perfectly equal.

HTH