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

Question

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?

possible duplicate: http://stackoverflow.com/questions/7884/testing-for-inequality-in-t-sql — ninesided, Sep 17 '10 at 07:15

score 10 · Answer 1 · edited Sep 17 '10 at 07:24

10

edited Sep 17 '10 at 07:24

onedaywhen

answered Sep 17 '10 at 07:01

gbn

3

Yup; NOT is somewhat different. It's for things like "Blank NOT IN (stuff...)" or "Blank NOT NULL" and such. – Andrew Barber Sep 17 '10 at 07:03
@Andrew Barber: thanks, good example, I was just working on an example but yours covers it nicely – gbn Sep 17 '10 at 07:08
1

I was under the impression that it was actually `<>` that was the ANSI standard inequality operator. – ninesided Sep 17 '10 at 07:14
1

@ninesided: http://msdn.microsoft.com/en-us/library/ms188074.aspx – gbn Sep 17 '10 at 07:16
1

@gbn: exactly, note the "not ISO standard" in parentheses next to `!=` on the linked table! – ninesided Sep 17 '10 at 07:20
@onedaywhen, @ninesided: in my humble defence, it's early and not enough coffee. Thanks. gbn – gbn Sep 17 '10 at 08:20

score 5 · Answer 2 · edited Sep 17 '10 at 07:26

5

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

edited Sep 17 '10 at 07:26

abatishchev

answered Sep 17 '10 at 07:15

alxx

yeah `when 'foo' = null` returns `null`, not `true` or `false` – Kirill Leontev Sep 17 '10 at 07:50
+1 @alxx: you could use `!=` or `<>` with the result of a COALESCE, though. – Sep 17 '10 at 13:21

2 Answers2