MySQL NOT

Last updated: October 19, 2026Oct 19, 2026 4 min read

The NOT operator reverses a condition: rows that would have matched are excluded, and rows that wouldn't are included. It is used on its own (NOT condition) and inside other operators: NOT IN, NOT LIKE, NOT BETWEEN, NOT EXISTS and IS NOT NULL.

1. How NOT Works

Table: flats (6 rows)

SELECT NOT TRUE AS not_true, NOT FALSE AS not_false, NOT NULL AS not_null, NOT (2 > 5) AS not_expr;

Result: 1 row

2. NOT on a Condition

SELECT flat_id, area, rent FROM flats WHERE NOT area = 'Baner';

Result: 4 rows

Use parentheses to negate a whole group of conditions:

SELECT flat_id, area, bhk FROM flats WHERE NOT (area = 'Baner' OR bhk = 1);

Result: 3 rows

3. NOT with Other Operators

SELECT flat_id, area FROM flats WHERE area NOT IN ('Baner', 'Wakad');

Result: 3 rows

SELECT flat_id, rent FROM flats WHERE rent NOT BETWEEN 20000 AND 30000;

Result: 4 rows

SELECT flat_id, area FROM flats WHERE area NOT LIKE '%d';

Result: 3 rows

SELECT flat_id, pets_ok FROM flats WHERE pets_ok IS NOT NULL;

Result: 4 rows

4. NOT with Boolean Columns

For a boolean column you can write NOT furnished. But flat 5's furnished is NULL (unknown), so it appears in neither list:

SELECT flat_id FROM flats WHERE furnished;

furnished

SELECT flat_id FROM flats WHERE NOT furnished;

NOT furnished

SELECT flat_id FROM flats WHERE furnished IS NOT TRUE;

IS NOT TRUE also catches NULL

5. De Morgan's Laws

Pushing NOT inside parentheses swaps AND and OR:

SELECT flat_id FROM flats WHERE NOT (bhk = 2 AND rent < 25000);

Result: 4 rows

SELECT flat_id FROM flats WHERE bhk <> 2 OR rent >= 25000;

Result: 4 rows

Same rows. The second form is often easier to read and to index.

6. NOT and NULL Together

Since NOT leaves NULL as NULL, "everything that isn't X" silently misses unknowns. Flats where pets aren't allowed:

SELECT flat_id, pets_ok FROM flats WHERE NOT pets_ok;

Result: 2 rows

Flats 3 and 6 (unknown) are missing. Decide whether unknown should count, and write it explicitly:

SELECT flat_id, pets_ok FROM flats WHERE NOT pets_ok OR pets_ok IS NULL;

Result: 4 rows

7. NOT EXISTS

CREATE TABLE visits (flat_id INT, visitor VARCHAR(20), visit_date DATE);
INSERT INTO visits VALUES (1,'Ravi','2026-03-01'),(2,'Sneha','2026-03-02'),(2,'Tara','2026-03-03'),(4,'Ravi','2026-03-04');

SELECT f.flat_id, f.area
FROM flats f
WHERE NOT EXISTS (SELECT 1 FROM visits v WHERE v.flat_id = f.flat_id);

Result: 3 rows

See MySQL EXISTS.

8. The ! Operator

MySQL also accepts ! as a synonym for NOT (deprecated in 8.0 in favour of NOT, and not standard SQL):

SELECT !TRUE AS bang_true, NOT TRUE AS not_true;

Result: 1 row

9. Practice Exercises

SELECT flat_id, area, bhk FROM flats WHERE NOT (bhk = 2 OR area = 'Kothrud');

SELECT flat_id, listed_on FROM flats WHERE listed_on NOT BETWEEN '2026-02-01' AND '2026-02-28';

10. Summary

• NOT reverses TRUE and FALSE; NOT NULL is still NULL.
• Use NOT IN, NOT LIKE, NOT BETWEEN, NOT EXISTS and IS NOT NULL for common cases.
• Negating conditions skips rows with NULLs; add OR col IS NULL or use IS NOT TRUE when needed.
• De Morgan: NOT (A AND B) = NOT A OR NOT B; NOT (A OR B) = NOT A AND NOT B.
• Prefer NOT over the deprecated !.