Lesson 9: Filtering results using the WHERE Clause and the NOT operator.
Flipping It Around.
When we use the AND operator in our WHERE clause we look for rows where ALL of the conditions are met.
When we use the OR operator, we look for rows where ANY of the conditions are met.
But what if we want to find out which rows DON'T meet the criteria we're filtering for? That's when we bring out the keyword NOT.
We want to go to a warm, sunny climate on holiday. Not somewhere we'd need ten layers of clothing and the only wildlife we'd see would be polar bears and penguins.
SELECT *FROM HolidaysWHERE Country NOT IN ('North Pole','South Pole');
What we are asking (or querying) the database for then is:
- All of the columns
- From the Holidays table
- Where the Country is not either the North Pole nor the South Pole.
With this SQL, we’ve told it:
- what we want
- where we want it from
- and been even more specific about which sub-group of the wider group we do NOT want to see.
This time we won't specifically ask for an exact match on eye color. But we do know that we don't want to see any blue OR brown eyes in our character line-up.
Use the NOT IN syntax to give that a try.