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

Either One with OR

At least one must be true.

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

Helen wants to know how many orders went wrong. An order went wrong if it was cancelled, or if it was returned. Either one counts. That's the job of or.

Either One with OR
From the video: Either One with OR
  • Which orders went wrong?
  • Cancelled, or returned
  • Either one counts

One of them is enough

With or, a row is kept if at least one test is true. Status equals cancelled, or status equals returned. Run it. Two hundred and three orders went wrong. That's worth looking into.

query.sqlsql
SELECT COUNT(*)
FROM orders
WHERE status = 'cancelled'
   OR status = 'returned';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ COUNT(*) โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 203      โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

The classic trap

Now a trap that catches almost everyone. We want thrillers or mysteries, under twelve dollars. Run it. That's wrong! There are thrillers here that cost more than twelve. The reason is that SQL does and before or, just like multiplication comes before addition in maths. So it read this as, any thriller, or a mystery under twelve.

query.sqlsql
SELECT title, genre, price
FROM books
WHERE genre = 'Thriller'
   OR genre = 'Mystery'
  AND price < 12;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        title        โ”‚  genre   โ”‚ price โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Code of the River   โ”‚ Thriller โ”‚ 12.99 โ”‚
โ”‚ Moonlight Express   โ”‚ Thriller โ”‚ 11.99 โ”‚
โ”‚ Murder at Mill Lane โ”‚ Mystery  โ”‚ 10.99 โ”‚
โ”‚ Deep Water          โ”‚ Thriller โ”‚ 13.49 โ”‚
โ”‚ Night Train to Oslo โ”‚ Thriller โ”‚ 14.99 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Brackets fix it

The fix is brackets. We put the two genre tests inside brackets, so they're checked together first. Then the price test applies to the whole group. Run it. Now we get only the two books that are a thriller or a mystery, and under twelve dollars. Whenever you mix and with or, use brackets. Always.

query.sqlsql
SELECT title, genre, price
FROM books
WHERE (genre = 'Thriller' OR genre = 'Mystery')
  AND price < 12;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        title        โ”‚  genre   โ”‚ price โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Moonlight Express   โ”‚ Thriller โ”‚ 11.99 โ”‚
โ”‚ Murder at Mill Lane โ”‚ Mystery  โ”‚ 10.99 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Or needs just one test to pass, and brackets keep and and or in the right order. Next, two shortcuts that make long lists of ors much shorter.

Key takeaways
  • OR needs one true test
  • AND runs before OR
  • Mixing them? Use brackets
Next up: Lists with IN and NOT โ€” shorter, cleaner filters