By ninesided


2008-08-11 15:56:42 8 Comments

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?

4 comments

@LAGARRIGUE Mattiheu 2010-05-20 15:25:54

Just a little adjustement fors those who come later:

The equality operator generate a unknow value when there is a null and the unknown value is treated a false. Not (unknown) is unknown

In the example below I'll try to say if a couple (a1, b1) is equal to (a2, b2). Note that each columns 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 we expect results :

  • Equal to be null
  • not equal to be not equal
  • different to be difFerent

but we get another result

  • Equal is null OK
  • Not Equal is null ???
  • Different is different

@Jamie Strauss 2013-11-04 01:47:37

This should be the correct answer

@SQLMenace 2008-08-11 16:02:43

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

@FistOfFury 2012-11-20 19:32:38

how do these clauses treat nulls? Are they all equivalent?

@Elaskanator 2018-10-29 19:58:22

@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 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.

@DannySmurf 2008-08-11 16:01:42

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

@Tim Sullivan 2008-08-11 15:58:48

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

HTH

Related Questions

Sponsored Content

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

30 Answered Questions

26 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

26 Answered Questions

[SOLVED] Check if table exists in SQL Server

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

11 Answered Questions

[SOLVED] Update a table using JOIN in SQL Server?

12 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

24 Answered Questions

[SOLVED] Find all tables containing column with specified name - MS SQL Server

16 Answered Questions

[SOLVED] How can I do an UPDATE statement with JOIN in SQL?

Sponsored Content