AI Hints
Homeโ€บSQL Tutorialโ€บCombining Tests
SQL ยท Lesson 12 of 126

Lists with IN and NOT

Shorter, cleaner filters.

โฑ 2 min readโ–ถ Video at 17:07๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Lists with IN and NOT
From the video: Lists with IN and NOT
  • 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.

query.sqlsql
SELECT COUNT(*)
FROM customers
WHERE country IN ('Ireland', 'Germany');
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
SELECT COUNT(*)
FROM customers
WHERE country NOT IN ('USA', 'UK');
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

โœ๏ธ Exercise: Your turn

Cooking or Business books priced over $20: show title, genre, price

Show hint

Use IN for the genres, AND for the price

Show solution
solution.sqlsql
SELECT title, genre, price
FROM books
WHERE genre IN ('Cooking', 'Business')
  AND price > 20;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚       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
Next up: Ranges with BETWEEN โ€” from this to that