NOT Operator
SQL · Filtering Rows
Theory
NOT reverses a condition: TRUE becomes FALSE and FALSE becomes TRUE. WHERE NOT dept = 'IT' keeps every employee outside IT, the same as dept <> 'IT'. NOT combines with other operators to form NOT IN, NOT BETWEEN, NOT LIKE, NOT EXISTS and IS NOT NULL.
The catch is NULL: NOT UNKNOWN is still UNKNOWN, so a row whose dept is NULL is not returned by either dept = 'IT' or NOT dept = 'IT'. Add OR dept IS NULL if you want those rows too. When negating a combined condition, De Morgan's rules apply: NOT (a AND b) equals NOT a OR NOT b, and NOT (a OR b) equals NOT a AND NOT b. Parentheses make it clear what NOT applies to.
Why we use it
Sometimes it is easier to describe what you do not want: everyone except one department, names not matching a pattern. NOT lets you exclude rows without listing all the other values.
When to use it
Use NOT to exclude a known set of rows. For simple inequality, <> is often clearer. Remember that NULLs are excluded by both a condition and its NOT, so handle them with IS NULL if they matter.
How it works
- Read a row
- Evaluate the inner condition
- Inner condition TRUE? Yes: NOT makes it FALSE: discard. No: Check if FALSE or UNKNOWN.
- Was it FALSE? Yes: NOT makes it TRUE: keep the row. No: UNKNOWN stays UNKNOWN: discard the row.
- Move to the next row
Syntax
SELECT columns FROM table
WHERE NOT condition;Example
SELECT * FROM employees WHERE NOT department = 'IT';Key points
- Can be combined with IN, LIKE, BETWEEN, EXISTS and NULL checks
- NOT dept = 'IT' is the same as dept <> 'IT'
- NOT UNKNOWN is still UNKNOWN, so NULL rows are excluded
- De Morgan: NOT (a AND b) = NOT a OR NOT b
- Use parentheses to show exactly what NOT negates