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

Combining Tests with AND

Both must be true.

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

Helen has a new idea. She wants to promote books that are cheap, and also have plenty of stock, so she won't run out. That's two tests at once. Price under fifteen, and stock over forty. To join two tests, we use the word and.

Combining Tests with AND
From the video: Combining Tests with AND
  • Cheap books with lots of stock
  • Two tests at once
  • Join them with AND

Two tests, both true

We write the first test after where, then and, then the second test. A row is only kept if both tests are true. Putting and on a new line, with a small indent, keeps it readable. Run it. Five books are cheap and well stocked. These are perfect for Helen's promotion.

query.sqlsql
SELECT title, price, stock
FROM books
WHERE price < 15
  AND stock > 40;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        title        โ”‚ price โ”‚ stock โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ The Silent Orchard  โ”‚ 14.99 โ”‚ 58    โ”‚
โ”‚ Stars Over Lagos    โ”‚ 9.99  โ”‚ 47    โ”‚
โ”‚ Deep Water          โ”‚ 13.49 โ”‚ 49    โ”‚
โ”‚ Night Train to Oslo โ”‚ 14.99 โ”‚ 45    โ”‚
โ”‚ Wild Coast          โ”‚ 11.49 โ”‚ 42    โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

AND is strict

Think of and as a strict guard. Every test must pass. A cheap book with only two copies fails. A well stocked book at thirty dollars fails. Only books that pass both get through. And you can keep adding more tests with more ands. Each one makes the result smaller.

Cheap, low stockfails
Pricey, lots of stockfails
Cheap, lots of stockpasses
More ANDsfewer rows

Summary

And means every test must be true. Next, its friendly cousin, or, which only needs one test to pass.

Key takeaways
  • AND joins tests
  • All must be true
  • More ANDs, fewer rows
Next up: Either One with OR โ€” at least one must be true