BeginnerLesson 3 of 305 min read

Filtering Rows with WHERE

Keep only the rows you want with comparisons, AND, OR, NOT, IN, BETWEEN and LIKE, and learn why brackets matter.

In this lesson you will

  • Filter rows with WHERE
  • Combine conditions with AND, OR and NOT
  • Use IN, BETWEEN and LIKE
  • Use brackets to avoid wrong results

About 25 minutes

Keeping only the rows you want

Maya wants the expensive products, the cheap plants and anything whose name starts with C. Each is the same job: keep some rows and drop the rest. The clause that does it is WHERE. It comes after FROM and holds a condition, a test that is true or not true for each row. Only rows for which the test is true appear in the result. If you leave WHERE out, you get every row.

Comparison tools

  • Comparisons. =, <> (not equal), <, >, <= and >=. Text values go in single quotes, such as ‘plant’.
  • AND, OR and NOT. AND needs both tests true, OR needs at least one, and NOT turns a test round.
  • IN. IN checks a value against a list, such as category IN (‘pot’, ‘care’).
  • BETWEEN. BETWEEN 5 AND 9 includes both ends, so 5 and 9 both count.
  • LIKE. LIKE matches text patterns. A percent sign stands for any run of characters, and an underscore stands for exactly one.

Six filters, run for real

The six queries below use these tools. The first keeps products priced over 10. The second needs both a category and a price. The third uses IN. The fourth finds names starting with C. The fifth uses BETWEEN. The sixth is a negation, written with NOT and brackets.

-- 1
SELECT name, price FROM products WHERE price > 10

-- 2
SELECT name, price FROM products WHERE category = 'plant' AND price < 10

-- 3
SELECT name, category FROM products WHERE category IN ('pot', 'care')

-- 4
SELECT name FROM products WHERE name LIKE 'C%'

-- 5
SELECT name, price FROM products WHERE price BETWEEN 5 AND 9

-- 6
SELECT name FROM products WHERE NOT (category = 'plant' OR price > 10)
-- 1
name | price
Fern | 12.50
Orchid | 18.00
Glass pot | 15.00
-- 2
name | price
Cactus | 6.00
Succulent mix | 9.00
-- 3
name | category
Clay pot | pot
Glass pot | pot
Plant food | care
Mist spray | care
-- 4
name
Cactus
Clay pot
-- 5
name | price
Cactus | 6.00
Clay pot | 8.50
Plant food | 5.50
Succulent mix | 9.00
-- 6
name
Clay pot
Plant food
Mist spray

Query 1 found 3 products priced over 10: Fern, Orchid, Glass pot. Query 2 found 2, the plants cheaper than 10. Query 4, names starting with C, returned 2 rows, the Cactus and the Clay pot. Query 5 is worth a close look. BETWEEN 5 AND 9 returned 4 products: Cactus, Clay pot, Plant food, Succulent mix. The Succulent mix costs exactly 9.00, and it is included, because BETWEEN counts both ends. Query 6 asked for products that are neither plants nor priced over 10, and found 3: Clay pot, Plant food, Mist spray.

AND before OR, and why brackets help

When a condition mixes AND with OR, SQL works out the AND parts first, just as multiplication comes before addition in arithmetic. So ‘a OR b AND c’ means ‘a, or both b and c’. If you meant ‘either a or b, and also c’, you must write brackets. The last query above uses brackets so that NOT applies to the whole pair of tests. Many wrong results come from a missing pair of brackets. Write them even when you are not sure they are needed.

Dates in a filter

Dates deserve a short note. The sample database stores dates as text in the year-month-day form, such as 2026-09-18. In that form, comparing dates as text puts them in the right order, so a condition such as order_date greater than or equal to ‘2026-09-01’ keeps orders from September onwards. Other styles, such as day first or month first, do not sort correctly as text. Many databases also have a proper date type with its own functions, and the names of those functions differ between systems, so check your documentation before copying a date example from another database.

Check every filter

  1. Read the condition aloud as a sentence: keep products that cost more than 10.
  2. Run it and count the rows. Does the number look reasonable?
  3. Look at the boundary values. If a price is exactly 10, should it be in or out?
  4. Run the opposite condition and check that the two results together make the whole table.
  5. Only then use the filter inside a bigger query.

Text matching varies

Text comparisons are a place where databases differ. Whether ‘Plant’ and ‘plant’ count as equal, and whether LIKE ignores capitals, depends on the database and how it is set up. In SQLite, LIKE ignores capitals for ordinary English letters, while some other systems do not by default. When capitals matter, check your database’s documentation and test with a small example.

Common mistake: quoting text wrongly

Writing a text value without quotes, or with double quotes. Text goes in single quotes. Double quotes are, in standard SQL, for names of columns and tables.

Made-up sample data

The products, names and prices are made up. SQL tools handle capitals in text, and some pattern characters, differently, so test text conditions in your own database.

Write a query for products with stock above 10 that are not plants. Then change the conditions to use brackets in two different ways and compare the results.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

Where does the WHERE clause go?

  1. Before SELECT
  2. After FROM
  3. After the semicolon
  4. Inside the table

The order is SELECT, FROM, WHERE.

What does BETWEEN 5 AND 9 include?

  1. Neither end
  2. Only 5
  3. Only 9
  4. Both 5 and 9

The Succulent mix at exactly 9.00 was in the result.

What does AND require?

  1. At least one condition
  2. Both conditions to be true
  3. No condition
  4. The first condition only

Use OR when either is enough.

What does LIKE with a percent sign match?

  1. Exactly one character
  2. A number
  3. Only capitals
  4. Any run of characters

The pattern C followed by a percent sign matches names starting with C.

Why write brackets around mixed AND and OR?

  1. AND is worked out before OR, so brackets make your meaning clear
  2. Brackets speed up the query
  3. SQL forbids OR without them
  4. They sort the rows

Brackets remove doubt about which tests belong together.

Summary

  • WHERE keeps only rows for which the condition is true.
  • BETWEEN includes both ends.
  • AND is worked out before OR; use brackets.
  • Text matching rules differ between databases.

Next lesson

Lesson 4 explains NULL, the value that makes simple filters misbehave.

Questions

Do I need quotes around numbers?

No. Numbers go without quotes. Text and dates go in single quotes.

Is LIKE case sensitive?

It depends on the database and its settings. SQLite ignores capitals for ordinary English letters, while others may not. Check your own system.

Are the products real?

No. They are made-up examples for a pretend plant shop.

Progress is saved in this browser.