Lesson 3 - Filtering Rows with Numbers

Welcome to Filtering Rows with Numbers#

So far you have chosen which columns to show. Now you will learn to choose which rows — the single most important skill in everyday SQL. Almost every real question is a filtering question: “Which orders lost money?”, “Which sales were over $10,000?”, “Which records are missing a value?”

The tool for this is the WHERE clause, and in this lesson you will use it with numeric comparisons, ranges, lists, missing-value checks, and combinations of conditions.

By the end of this lesson, you will be able to:

• Filter rows with the WHERE clause and comparison operators (>, <, =, <>, >=, <=)
• Explain SQL’s order of execution and why aliases cannot be used in WHERE
• Match a range of values with BETWEEN and a list of values with IN
• Find missing values using IS NULL and IS NOT NULL
• Combine multiple conditions with AND and OR

You should be comfortable with SELECT, derived columns, and aliases. Let’s begin.

The WHERE Clause and Comparison Operators#

Many business questions boil down to comparing numbers: Did sales beat the target? Are we under budget? The WHERE clause answers them by keeping only the rows where a condition is true.

Filtering with WHERE#

WHERE comes after FROM and before LIMIT. The clauses you have learned always appear in this order:

1. SELECT — which fields to show
2. FROM — which table they come from
3. WHERE — which records to keep
4. LIMIT — how many records to return

Here is WHERE finding every order with sales over $10,000:

SELECT order_id, category, product_name, quantity, sales
  FROM orders
 WHERE sales > 10000;

Every kept row has sales greater than 10,000 — they are all high-end copiers and a videoconferencing unit. The WHERE clause acts as a filter, returning only the records where the condition evaluates to TRUE.

More Comparison Operators#

> and < are just the start. The full set:

These work on numbers and, in the case of = and <>, on text too. Text values must be wrapped in single quotes. For example, to exclude the Consumer segment:

SELECT order_id, segment, product_name
  FROM orders
 WHERE segment <> 'Consumer';

Only “Corporate” and “Home Office” remain. Be aware that text comparisons are case sensitive: WHERE region = 'WEST' returns nothing, because the data stores 'West', not 'WEST'. And to filter for a loss, you compare against a negative number — WHERE profit < -1000 finds orders that lost more than $1,000.

Order of Execution and Aliases#

As you add clauses, it helps to know the order in which SQL actually runs them — which is different from the order you write them.

The Execution Order#

1. FROM — SQL first figures out which table it is reading.
2. WHERE — it filters the rows.
3. SELECT — only now does it pick the columns (and compute aliases).
4. LIMIT — finally it trims the row count.

Here is why this matters: because SELECT runs after WHERE, an alias defined in SELECT does not exist yet when WHERE runs. So you cannot filter on an alias — you must repeat the raw calculation. For example, to find items cheaper than $0.50 per unit:

SELECT order_id, sub_category, product_name,
       sales / quantity AS price_per_unit
  FROM orders
 WHERE sales / quantity < 0.50;

The SELECT aliases the calculation as price_per_unit, but the WHERE clause uses the raw sales / quantity again. (SQLite is unusually lenient and will accept the alias here, but most dialects reject it, so repeating the calculation is the portable, recommended habit.)

Matching Ranges and Lists#

Comparison operators check one value against another. But often you want every row inside a range or matching one of several specific values.

BETWEEN for Consecutive Ranges#

BETWEEN matches everything in a continuous range, and it is inclusive of both endpoints. To find orders where 2 to 4 units were sold:

SELECT order_id, product_name, quantity
  FROM orders
 WHERE quantity BETWEEN 2 AND 4;

Quantities of 2, 3, and 4 are all kept — BETWEEN 2 AND 4 includes both ends.

IN for Non-Consecutive Lists#

What if the values you want are not a continuous range? IN matches any value in a list. Superstore’s usual discounts are multiples of 10%, but three odd ones exist — 15%, 32%, and 45%. To find just those:

SELECT order_id, category, sub_category, product_name, sales, discount
  FROM orders
 WHERE discount IN (0.15, 0.32, 0.45);

IN (0.15, 0.32, 0.45) keeps any row whose discount equals one of those three values. Interestingly, every result is Furniture — those unusual discounts cluster around bookcases and tables.

Finding Missing Values with NULL#

Real data is full of gaps. SQL marks a missing value with the special keyword NULL. Handling it correctly trips up many beginners, so let’s get it right.

Why = NULL Fails#

Your instinct might be to write WHERE postal_code = NULL:

SELECT order_id, postal_code
  FROM orders
 WHERE postal_code = NULL;

This returns zero rows — even if missing postal codes existed, this would never find them. The reason is three-valued logic: a SQL condition can be TRUE, FALSE, or unknown. Think of NULL as “unknown.” You cannot say an unknown value equals (or does not equal) anything, so = NULL is itself unknown, and WHERE only keeps rows that are TRUE.

Using IS NULL and IS NOT NULL#

The correct way to test for missing values is the IS and IS NOT operators:

SELECT order_id, postal_code
  FROM orders
 WHERE postal_code IS NULL;

In our superstore database this also returns zero rows — but for a very different reason: every order genuinely has a postal code, so there is nothing missing to find. That is itself a useful data-quality result. To prove the column is fully populated, flip the test to IS NOT NULL, which returns rows:

SELECT order_id, postal_code
  FROM orders
 WHERE postal_code IS NOT NULL
 LIMIT 5;

Remember this rule whenever you hunt for data-quality problems — on a messier dataset, WHERE segment IS NULL would reveal which customers have no segment recorded.

Combining Conditions with AND and OR#

A single filter is rarely enough. AND and OR let you stack conditions.

Narrowing with AND#

AND keeps only rows that satisfy every condition. The more ANDs you add, the more selective — and smaller — your result becomes. To find Office Supplies orders in the West region:

SELECT order_id, product_id, product_name, sales
  FROM orders
 WHERE category = 'Office Supplies'
   AND region = 'West';

Only rows that are both Office Supplies and in the West survive. You can combine numeric and text conditions freely — for instance, WHERE sub_category = 'Storage' AND sales / quantity > 300 finds the priciest storage items.

Broadening with OR#

OR keeps rows that satisfy at least one condition, so it generally returns more rows. To list every transaction that is either Furniture or over $100:

SELECT order_id, category, product_name, sales
  FROM orders
 WHERE category = 'Furniture' OR sales > 100;

Notice the mix of categories: the Office Supplies and Technology rows are kept because their sales exceed $100, while the Furniture chair would qualify on the category condition alone (it also happens to be over $100). With OR, satisfying either condition is enough — a Furniture item under $100 would still appear, kept by the category check. A query like WHERE profit < 0 OR quantity = 1 would flag products that are either unprofitable or only ever sold one at a time.

Practice Exercises#

Use the orders table.

Exercise 1: Find Big Losses#

Write a query returning order_id, product_name, sales, discount, and profit, keeping only orders that lost more than $1,000 in profit.

Exercise 2: Sales Tax in a Range#

Write a query returning order_id, product_id, and a sales_tax field equal to 10% of sales. Keep only rows where that sales tax is between $1 and $2.

Exercise 3: Missing Segments#

Write a query that returns all columns for every row where the segment field is missing. (In this clean dataset the result is empty — which confirms every order has a segment. Then flip the test to IS NOT NULL and add LIMIT 5 to see real rows.)

Summary#

You learned to control which rows appear in your results. You filtered with comparison operators, matched ranges and lists, correctly handled missing values, and combined multiple conditions — the everyday core of SQL analysis.

Key Concepts#

• WHERE — keeps only rows where a condition is TRUE; sits between FROM and LIMIT.
• Comparison operators — >, <, =, <>, >=, <=; text comparisons are case sensitive and need single quotes.
• Order of execution — FROM, WHERE, SELECT, LIMIT; aliases are unavailable in WHERE, so repeat the raw calculation.
• BETWEEN — matches an inclusive, consecutive range.
• IN — matches any value in a list (numbers or text).
• IS NULL / IS NOT NULL — the correct way to test for missing values; = NULL never works because of three-valued logic.
• AND — keeps rows meeting every condition (fewer results).
• OR — keeps rows meeting at least one condition (more results).

Why This Matters#

Datasets are huge, but the answer to a business question is almost always a small slice of them. Filtering is how you carve that slice. Whether you are isolating unprofitable orders, auditing missing data, or pulling one customer’s history, the WHERE clause is the tool you reach for first. Getting NULL handling and AND/OR logic right is what makes the difference between a query that quietly returns the wrong rows and one your team can trust. These patterns will show up in essentially every query you ever write.

Next Steps#

Continue Building Your Skills#

Filtering is the heartbeat of SQL, and you now know how to do it precisely. Practice stacking conditions with AND and OR, and make IS NULL second nature — these habits will save you from subtle, hard-to-spot mistakes. Next you will take filtering into the world of text and categories.