Lists with IN and NOT
Shorter, cleaner filters.
Helen is planning a delivery deal for Ireland and Germany. You could write country equals Ireland, or country equals Germany. But imagine ten countries. That's a lot of ors. There's a much neater way.

- A deal for two countries
- Lots of ORs get messy
- A neater way
IN a list
In checks whether a value is anywhere in a list. The list goes in brackets, with commas between the items. It means exactly the same as a chain of ors, but it's shorter and easier to read. Run it. Ninety four customers live in Ireland or Germany.
SELECT COUNT(*)
FROM customers
WHERE country IN ('Ireland', 'Germany');โโโโโโโโโโโโ โ COUNT(*) โ โโโโโโโโโโโโค โ 94 โ โโโโโโโโโโโโ
NOT flips a test
Not flips a test around. Not in means, the value is not in the list. Here we find customers who live outside the USA and the UK. Run it. Two hundred and fifty seven customers. You can put not in front of other tests too, like not between, and not like, which we'll meet next.
SELECT COUNT(*)
FROM customers
WHERE country NOT IN ('USA', 'UK');โโโโโโโโโโโโ โ COUNT(*) โ โโโโโโโโโโโโค โ 257 โ โโโโโโโโโโโโ
Your turn
Your turn. Find cooking or business books that cost more than twenty dollars. Show the title, genre, and price. Pause and try it. Here's one way. Genre in cooking and business, and price greater than twenty.
Cooking or Business books priced over $20: show title, genre, price
Show hint
Use IN for the genres, AND for the price
Show solution
SELECT title, genre, price
FROM books
WHERE genre IN ('Cooking', 'Business')
AND price > 20;โโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโฌโโโโโโโโ โ title โ genre โ price โ โโโโโโโโโโโโโโโโโโโโโผโโโโโโโโโโโผโโโโโโโโค โ The Quiet Kitchen โ Cooking โ 24.99 โ โ Numbers Don't Lie โ Business โ 21.99 โ โ Spice Roads โ Cooking โ 27.99 โ โ One Pan Dinners โ Cooking โ 21.49 โ โ The Pricing Game โ Business โ 23.99 โ โโโโโโโโโโโโโโโโโโโโโดโโโโโโโโโโโดโโโโโโโโ
Summary
In checks a list, and not flips a test. Your filters are getting powerful. Next, ranges, patterns, and the strange case of missing values.
Key takeaways- IN ( ... ) checks a list
- NOT IN excludes a list
- NOT flips any test