Testing for inequality in T-SQL
Solution 1
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
Solution 2
Note that the != operator is not standard SQL. If you want your code to be portable (that is, if you care), use <> instead.
Solution 3
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.
Solution 4
There will be no performance hit, both statements are perfectly equal.
HTH
Related videos on Youtube
Comments
-
ninesided over 2 years
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?-
Dinah about 15 yearsSee also: stackoverflow.com/questions/723195/…
-
Elaskanator over 5 yearsThe syntax
NOT (...)
is bad practice when the code can be easily negated to be in the affirmative or de Morgan'd due to readability. Like, you shouldn't write code likeIF (NOT(A)) THEN [x] ELSE [y] END
when you could just writeIF (A) THEN [y] ELSE [x] END
-
-
FistOfFury over 11 yearshow do these clauses treat nulls? Are they all equivalent?
-
Elaskanator over 5 years@FistOfFury, Comparing with NULL always returns NULL and prevents matches (unless you changed the ANSI NULLs setting), so you will never get records where
au_id
is null, nor will you ever get records if@id
is null. In that case, you need to actually writeWHERE ... IS NULL
orWHERE ... IS NOT NULL
It helps me to think ofNULL
as "unknown" then it makes sense: You don't know if@id
is the same as an unknown value! tl;dr Yes they are equivalent even in the case of NULLs.