Testing for inequality in T-SQL

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

These 3 will get the same exact execution plan

declare @id varchar(40)
select @id = '172-32-1176'

select * from authors
where au_id <> @id

select * from authors
where au_id != @id

select * from authors
where not (au_id = @id)

It will also depend on the selectivity of the index itself of course. I always use au_id <> @id myself

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

HTH

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.