Turn a condition inside out

01 / 05

Write "is not" as it sounds

NOT LIKE gave you "does not contain". As it happens, NOT is not just for LIKE. It goes in front of any condition at all.

Write WHERE NOT condition and back come the rows that do not match it.

SELECT * FROM shops  WHERE NOT floor = 1;

Result

2 | Lane Bookshop | books | 2 | 3
5 | Sunny Bookshop | books | 2 | 5
6 | Lane Cafe | cafe | 3 | 7

02 / 05

You can put NOT on a > or a < as well. NOT staff >= 5 means "not five or more", so four or fewer.

Sometimes it is safer to write the condition as you were told it and add a NOT than to flip it around in your head.

SELECT name, staff FROM shops  WHERE NOT staff >= 5;

Result

Bluesky Bread | 4
Lane Bookshop | 3
Anchor Flower Store | 2

03 / 05

NOT grabs the one condition immediately to its right. When there are two, make it clear with ( and ) how much you meant to cancel.

They look alike and behave nothing alike. When in doubt, bracket it.

SELECT name, kind, floor FROM shops  WHERE NOT (kind = 'books' AND floor = 2);

04 / 05

NOT kind = 'books' and kind <> 'books' come out the same. Either is fine.

Pick whichever reads better for whoever comes next.

SELECT name, kind FROM shops  WHERE NOT kind = 'books';

Result

Bluesky Bread | bread
Sunset Bread Workshop | bread
Anchor Flower Store | flowers
Lane Cafe | cafe

05 / 05

When you use NOT, it helps to see whether the two results add up to the whole table.

kind = 'books' gives 2, NOT kind = 'books' gives 4, and the table has 6 rows. That adds up.

If your "everything else" feels too big or too small, check that sum. Let us write some.