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.

Answer 3

!= is a binary operator that returns true if its two arguments are not equal to each other.

NOT is a unary operator, which reverses its argument, a Boolean expression.

For example, this expression: a < 10 is true when a is any value less than 10. This condition can be negated: NOT a < 10. Negating this condition makes it true in the opposite cases, i.e. when a not less than 10. It's the same as a >= 10.

The expression a != 10 is true when a is any value less than 10 or any value greater than 10. This is a completely different case from a condition negated with NOT.

Answer 4

Both NOT operator and != almost serve a similar purpose.Both are used in Where clause of an sql query.

NOT operator shows records when a particular condition is not true. Example:

SELECT * FROM Employees
WHERE NOT Country='Germany'

will get you records with all employees with countries other than Germany.

The != operator similarly checks if the values of two operands are equal or not, if values are not equal then condition becomes true.

Example:

SELECT * FROM Employees
WHERE Country!='Germany'

will get you all rows with country column having country other than Germany.