Testing for inequality in T-SQL

user_1030 0
Viewed 22116

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?

inequality indexing tsql
4 Answers

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

user_7400

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

DannySmurf_10280

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

HTH

user_7220
Related