All lessons

Module 2: Picking rows and columns · Lesson 2.2 · about 25 min

WHERE: choosing rows

Keep only the rows you want with WHERE: compare numbers, text and dates, and learn the small traps (quotes, capitals, stray spaces) that make a filter miss rows.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 2 · about 25 minutes · read first: 2.1. You’ll be able to keep only the rows you need with WHERE, compare numbers, text and dates correctly, and spot a filter that silently misses rows.

WHERE gives every row a yes-or-no test and keeps only the rows that pass.

In everyday terms: a bouncer with a guest list. Each person at the door is checked on their own; the ones on the list go in, everyone else stays outside. Nobody’s chances depend on the person behind them.

1. Why this matters

Almost every business question starts with “only”: only last month, only Ontario, only completed orders. WHERE is how you say “only”. Getting it slightly wrong is the most common way an analyst’s number disagrees with finance’s, so it pays to know exactly what WHERE keeps and what it drops.

2. WHERE keeps rows that pass a test

WHERE goes after FROM. It holds a condition: a test that is true or false for each row. Rows where it is true are kept; the rest are dropped.

-- try
SELECT order_id, item, amount
FROM mini_orders
WHERE amount > 40

Press Show how it runs, then Play. At the WHERE step watch the Bagels ($5) and Puzzle ($20) rows fade out: 6 rows go in, 4 come out. Note that WHERE runs before SELECT, so it can test any column of the table, even one you don’t show.

WHERE condition
Keep the rows where the condition is true.
= , <>
Equal, not equal (also written !=).
>, >=, <, <=
Greater, greater or equal, less, less or equal. The "or equal" ones include the boundary value.
'text'
Text values go in single quotes; without them SQL looks for a column.
Physical meaning
WHERE never changes a row. It removes the rows that fail, so the answer can only have the same number of rows or fewer, and it runs before SELECT, so it can test columns you don't show.

”> 45 and >= 45 are basically the same.” They differ by exactly the rows equal to 45, and prices often sit right on round numbers. In mini_orders, amount > 45 keeps 2 rows and amount >= 45 keeps 4 (both kettles cost $45). Decide on purpose whether the boundary is in.

Watch it happen, one step at a time (the live box’s Show how it runs button animates the real query):

FROM mini_orders.
order_iditemamount
101Kettle45
102Bagels5
103Puzzle20
104Duvet90
105Kettle45
106Speaker60
Test amount > 40 on each row.
order_idamountpasses?
10145yes
1025no
10320no
10490yes
10545yes
10660yes
Keep the passes. 6 rows in, 4 out.
order_iditemamount
101Kettle45
104Duvet90
105Kettle45
106Speaker60

3. The comparison operators

Operator Means Example
= equal store_id = 12
<> or != not equal status <> 'completed'
> >= greater, greater or equal unit_price >= 50
< <= less, less or equal quantity < 2

Change > to >= and the number to 45 below, and watch the row count change.

-- try
SELECT product_name, unit_price
FROM products
WHERE unit_price > 50

Four products cost more than $50.

4. Text goes in single quotes

Text values go in single quotes. Without quotes, SQL thinks completed is a column name and errors:

-- fails
SELECT order_id, status
FROM orders
WHERE status = completed

Fixed:

-- try
SELECT order_id, order_date, store_id
FROM orders
WHERE status = 'cancelled'

119 orders were cancelled. (Double quotes are for column names in standard SQL. SQLite is forgiving and may accept “cancelled”, but SQL Server and PostgreSQL won’t treat it as text, so get the habit right now.)

5. Exact means exact

= on text compares character by character. In SQLite, capitals count, and so do spaces you can’t see.

-- try
SELECT COUNT(*) AS exact_match
FROM customers
WHERE city = 'Winnipeg'

That gives 59. But two more Winnipeg customers were typed as 'Winnipeg ' with a trailing space, so they’re missed. Try WHERE city = 'winnipeg' (lowercase): 0 rows. A safer filter trims and lowercases first; you’ll meet TRIM and LOWER properly in Lesson 7.2.

-- try
SELECT COUNT(*) AS all_winnipeg
FROM customers
WHERE LOWER(TRIM(city)) = 'winnipeg'

SQL Server usually ignores capitals in comparisons (it depends on the database’s collation); PostgreSQL, like SQLite, does not.

6. Dates

Northbay’s dates are text in YYYY-MM-DD form, which sorts in date order, so <, > and = work as you’d hope. Always write the full date in quotes.

-- try
SELECT store_name, opened_on
FROM stores
WHERE opened_on < '2018-01-01'

Three stores opened before 2018.

Maths and principle

Principle: WHERE is selection, written σ. It applies a predicate (a yes/no test) to each row on its own and keeps the rows where the answer is true. One row’s fate never depends on another row.

Equation: σp(T) = { r ∈ T : p(r) is true }. So |σp(T)| ≤ |T|: a filter can only keep or remove rows, never add them. Two filters in a row are the same as one with both tests: σp(σq(T)) = σp AND q(T).

Analogy: a sieve. Each grain (row) either falls through or stays, depending only on its own size. It breaks down a little with NULL, where the test can be neither true nor false (Lesson 2.4); such rows are dropped too.

Try it: σamount > 40(mini_orders) has 4 rows. How many does σamount > 100 have? (0. An empty answer is still a valid table, with columns and no rows.)

7. Worked example

Question: how busy was Christmas Eve 2025, and which channel did those orders come through?

  1. Filter to one day: WHERE order_date = '2025-12-24'.
  2. Show the order, store and channel so you can read them.
-- try
SELECT order_id, store_id, channel, status
FROM orders
WHERE order_date = '2025-12-24'
  1. Count: 8 rows, so 8 orders. Read down the channel column to see the split, then try '2024-12-24' for the year before.

8. Practice

-- exercise: List the order_id and order_date of every order placed at store 7 (Thunder Bay).
-- hint: WHERE store_id = 7. Numbers need no quotes.
SELECT order_id, order_date
FROM orders
-- answer:
SELECT order_id, order_date
FROM orders
WHERE store_id = 7
-- exercise: Find every product that costs less than $3 on the shelf. Show its name and unit_price.
-- hint: WHERE unit_price < 3
SELECT product_name, unit_price
FROM products
WHERE unit_price < 30
-- answer:
SELECT product_name, unit_price
FROM products
WHERE unit_price < 3
-- exercise: List the first name, last name and joined_on date of customers who joined on or after 1 December 2025.
-- hint: Dates are text in quotes: '2025-12-01'. On or after means >=.
SELECT first_name, last_name, joined_on
FROM customers
WHERE joined_on > '2025-12-31'
-- answer:
SELECT first_name, last_name, joined_on
FROM customers
WHERE joined_on >= '2025-12-01'
  • WHERE tests each row on its own and keeps the rows where the test is true.
  • A filter can only keep or drop rows; it never adds or changes them.
  • > leaves out the boundary value; >= keeps it.
  • Text goes in single quotes, and = on text is exact: capitals and hidden spaces count.
  • ISO dates (YYYY-MM-DD) compare correctly with < and >.

New words: condition, selection, predicate, comparison operator, collation.

Check yourself

1Which is the right way to filter on text?
2What does WHERE amount > 40 do to the rows where amount is 40?
3Why might WHERE city = 'Winnipeg' miss some Winnipeg customers?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.