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?

See also: http://stackoverflow.com/questions/723195/should-i-use-or-for-not-equal-in-tsql — Dinah, May 12 '09 at 15:55
The syntax `NOT (...)` is bad practice when the code can be easily negated to be in the affirmative or [de Morgan](https://en.wikipedia.org/wiki/De_Morgan%27s_laws)'d due to readability. Like, you shouldn't write code like `IF (NOT(A)) THEN [x] ELSE [y] END` when you could just write `IF (A) THEN [y] ELSE [x] END` — Elaskanator, Oct 29 '18 at 20:13

score 44 · Accepted Answer · edited Nov 10 '09 at 05:41

44

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

edited Nov 10 '09 at 05:41

John Gietzen

48,783
32
145
190

answered Aug 11 '08 at 16:02

SQLMenace

132,095
25
206
225

6

how do these clauses treat nulls? Are they all equivalent? – FistOfFury Nov 20 '12 at 19:32
2

@FistOfFury, Comparing with NULL always returns NULL and prevents matches (unless you [changed the ANSI NULLs setting](https://stackoverflow.com/questions/9766717)), 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 write `WHERE ... IS NULL` or `WHERE ... IS NOT NULL` It helps me to think of `NULL` 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. – Elaskanator Oct 29 '18 at 19:58

score 30 · Answer 2 · answered Aug 11 '08 at 16:01

30

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

answered Aug 11 '08 at 16:01

DannySmurf

547
4
3

score 12 · Answer 3 · edited Feb 07 '22 at 19:09

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.

score 5 · Answer 4 · answered Aug 11 '08 at 15:58

5

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

HTH

answered Aug 11 '08 at 15:58

Tim Sullivan

16,808
11
74
120

Testing for inequality in T-SQL

4 Answers4

Logic Hazard On Equality to Null To Be Considered

Linked

Related