What is the difference between NOT and != operators in SQL?

Question

What is the difference between NOT and != operators in SQL? I can't understand the difference. I guess they are same.

Answer 1

NOT negates the following condition so it can be used with various operators. != is the non-standard alternative for the <> operator which means "not equal".

e.g.

NOT (a LIKE 'foo%')
NOT ( (a,b) OVERLAPS (x,y) )
NOT (a BETWEEN x AND y)
NOT (a IS NULL)

Except for the overlaps operator above could also be written as:

a NOT LIKE 'foo%'
a NOT BETWEEN x AND y
a IS NOT NULL

In some situations it might be easier to understand to negate a complete expression rather then rewriting it to mean the opposite.

---

NOT can however be used with <> - but that wouldn't make much sense though: NOT (a <> b) is the same as a = b. Similarly you could use NOT to negate the equality operator NOT (a = b) is the same as a <> b

Answer 2

This question actually makes a lot more sense than people give it credit for.

Firstly, original SQL not-equal operator was <>, and only later on the C-style != was added as far as I know. I personally always use <> as != looks strange to me, but I'm old school.

Secondly, of course the original asker didn't mean to compare NOT with !=, but rather the difference between NOT a = b vs. a != b. And intuitively there should be a difference, but for all I know there isn't.

To make this all clear, here is an example session run on PostgreSQL (in Oracle you need more weird stuff such as SELECT ... FROM DUAL UNION ..., etc., which I avoid for the sake of brevity):

db=#  with tst(a, b) as ( values (1,2), (2,3), (4, null) ) select * from tst;
 a | b
---+---
 1 | 2
 2 | 3
 4 |
(3 rows)

db=#  with tst(a, b) as ( values (1,2), (2,3), (4, null) ) select * from tst where b = 2;
 a | b
---+---
 1 | 2
(1 row)

db=#  with tst(a, b) as ( values (1,2), (2,3), (4, null) ) select * from tst where b != 2;
 a | b
---+---
 2 | 3
(1 row)

db=#  with tst(a, b) as ( values (1,2), (2,3), (4, null) ) select * from tst where not b = 2;
 a | b
---+---
 2 | 3
(1 row)

Here we may think that this last query should also have returned the row (4, NULL). But it didn't. In PostgreSQL I can actually inspect this further, as follows:

db=#  with tst(a, b) as ( values (1,2), (2,3), (4, null) ) select *, b = 2 as beq2 from tst;
 a | b | beq2
---+---+------
 1 | 2 | t
 2 | 3 | f
 4 |   |
(3 rows)

You see that the Boolean expression b = 2 is NULL for the case where b is NULL. However, when a Boolean expression is NULL it is treated as false, or rather not true. And when you negate it with NOT, the Boolean value of the expression stays NULL and therefore is still not true.

Unfortunately I know of no other way than to handle NULL cases explicitly, so I have to write:

db=#  with tst(a, b) as ( values (1,2), (2,3), (4, null) ) select * from tst where b is null or b = 2;
 a | b
---+---
 1 | 2
 4 |
(2 rows)

So, instead of writing NOT <Boolean expression> you always have to write a IS NULL OR b IS NULL OR ... OR z IS NULL OR f(a, b, ..., z) where a, b, ..., z are variables in the given Boolean expression f(...).

It would be so much easier if instead of just NOT there were the Boolean operators MAYBE and CANNOT. So you could write WHERE MAYBE b = 2 or WHERE CANNOT b = 2 instead of this complicated OR combination of a bunch of IS NULL tests before your actual condition.