AI Hints
Homeโ€บSQL Tutorialโ€บFiltering Rows
SQL ยท Lesson 7 of 126

Filtering with WHERE

Only the rows you want.

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

Helen wants to run a sale table at the front of the shop, with books under ten dollars. She doesn't want all thirty books. She only wants the cheap ones. To keep some rows and skip the others, we use a new word. Where.

Filtering with WHERE
From the video: Filtering with WHERE
  • A sale table for cheap books
  • Not all thirty, just some
  • Meet WHERE

Books under ten dollars

Where goes after from. It's followed by a test. Here the test is, price is less than ten. SQL checks that test on every single row, and only keeps the rows where it's true. Run it. Just two books pass the test. Stars Over Lagos, and Garden of Small Things.

query.sqlsql
SELECT title, price
FROM books
WHERE price < 10;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚         title          โ”‚ price โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Stars Over Lagos       โ”‚ 9.99  โ”‚
โ”‚ Garden of Small Things โ”‚ 8.99  โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

How WHERE works

Picture it like this. The rows come in one at a time. Each one meets the test. If the test is true, the row goes through. If it's false, the row is dropped. Select then decides which columns of the surviving rows you see.

The expensive end

The test can point the other way too. Greater than or equal to twenty five finds the most expensive books. Run it. Four books, and three of them are about technology or data. That's a useful thing for Helen to know.

query.sqlsql
SELECT title, price
FROM books
WHERE price >= 25;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          title           โ”‚ price โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Python for Curious Minds โ”‚ 34.99 โ”‚
โ”‚ SQL in a Weekend         โ”‚ 29.99 โ”‚
โ”‚ Spice Roads              โ”‚ 27.99 โ”‚
โ”‚ Data for Everyone        โ”‚ 31.99 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Where keeps only the rows that pass a test. You've just answered Helen's sale question. Next, filtering on words instead of numbers, which has one small trap.

Key takeaways
  • WHERE comes after FROM
  • A test runs on every row
  • True rows are kept
Next up: Filtering on Text โ€” quotes and capitals