and it worked fine. Then one day you tried WHERE bonus != 1000 and some rows mysteriously went missing. The reason is the difference between != and IS NOT. Let me explain it in plain English.
The Short Answer
-
!= (also written <>) compares values. Use it for text, numbers, and dates.
-
IS NOT compares against NULL, TRUE, or FALSE. In real life you will almost always use it as IS NOT NULL.
They are not interchangeable, and the reason is how SQL treats NULL.
Using != for Normal Values
This is the one you use most of the time. It checks if a value is different from another value.
WHERE status != 'borrowed' -- status is not the text "borrowed"
WHERE id != 5 -- id is not 5
WHERE rating != 8.9 -- rating is not 8.9
Simple. If the column has a real value, != does exactly what you expect.
Using IS NOT for NULL
NULL in SQL does not mean "empty." It means "unknown" or "missing." And here is the catch: you cannot compare anything to "unknown" using = or !=, because the result would also be unknown.
So this silently breaks:
WHERE status != NULL -- returns NO rows, ever
Even a row where status is truly missing will not match, because something != NULL becomes NULL (unknown), and SQL only keeps rows where the condition is TRUE.
The correct way:
WHERE status IS NOT NULL -- works as you would expect
Quick Reference Table
| You want to check |
Use |
| Value is different from another value |
!= or <>
|
| Column is not missing |
IS NOT NULL |
| Column is missing |
IS NULL |
| Value equals another value |
= |
A Real Gotcha Worth Remembering
Say you have a bonus column. Some employees have a number, and some have NULL because no bonus was recorded. You want everyone whose bonus is not 1000.
You might write this:
WHERE bonus != 1000
This will miss every employee with a NULL bonus, even though their bonus is clearly not 1000. To include them you need:
WHERE bonus != 1000 OR bonus IS NULL
This exact problem shows up a lot in practice (the classic "Employee Bonus" type query), so it is worth burning into memory.
Bonus: A Real Query
Here is a small example. You run a library and you want to report books with an odd ID whose status is not "borrowed", ordered by rating so the best available books show first.
SELECT *
FROM Books
WHERE id % 2 != 0
AND status != 'borrowed'
ORDER BY rating DESC;
A couple of small notes:
-
!= works in MySQL, but the official SQL standard operator is <>. Both do the same job in MySQL.
- Use single quotes (
'boring') for strings. Double quotes work in MySQL but single quotes are safer if you ever switch databases.
Wrap Up
Remember it like this: != is for things that have a value, and IS NOT NULL is for checking that a value even exists. The moment NULL enters the picture, switch from != to IS NOT. That one habit will save you from a lot of confusing "where did my rows go" moments.