DEV Community

Mohamed Idris
Mohamed Idris

Posted on

SQL: When to Use != and When to Use IS NOT

#tutorial #beginners #database #sql

If you are learning SQL, you have probably written something like WHERE status != 'borrowed' 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)