SQL fundamental question '!=' vs '<>' vs 'Not'

146

+6 Q:

Possible Duplicate:
Testing for inequality in T-SQL

Hi,

does there any different to use the '!=' vs '<>' vs 'Not'?

which one will have the worst performance

or it exist just because of the backward compatibility syntax?

+10 A:

gbn 2010-09-17 07:01:01

Yup; NOT is somewhat different. It's for things like "Blank NOT IN (stuff...)" or "Blank NOT NULL" and such.

Andrew Barber 2010-09-17 07:03:36

@Andrew Barber: thanks, good example, I was just working on an example but yours covers it nicely

gbn 2010-09-17 07:08:04

I was under the impression that it was actually `<>` that was the ANSI standard inequality operator.

ninesided 2010-09-17 07:14:34

@ninesided: http://msdn.microsoft.com/en-us/library/ms188074.aspx

gbn 2010-09-17 07:16:26

@gbn: exactly, note the "not ISO standard" in parentheses next to `!=` on the linked table!

ninesided 2010-09-17 07:20:31

@onedaywhen: well done

ninesided 2010-09-17 07:25:27

@onedaywhen, @ninesided: in my humble defence, it's early and not enough coffee. Thanks. gbn

gbn 2010-09-17 08:20:49

+5 A:

AFAIK, if you want to check for NULL, you must use IS NOT and not != or <>

alxx 2010-09-17 07:15:06

yeah `when 'foo' = null` returns `null`, not `true` or `false`

be here now 2010-09-17 07:50:24

+1 @alxx: you could use `!=` or `<>` with the result of a COALESCE, though.

Mark Bannister 2010-09-17 13:21:25

ansaurus