Filtering With Where

Why this matters

Business Analysts answer concrete questions like: Which customers signed up this quarter? How many paid orders were refunded? Which accounts are active but not verified? The WHERE clause lets you filter rows to the exact slice needed for decisions, dashboards, and QA checks.

• Build precise cohorts (e.g., trial users in the last 30 days).
• Focus on relevant transactions (e.g., orders between $100–$500).
• Exclude noise (test data, NULLs, canceled records).
• Check data quality quickly (missing fields, outliers).

Concept explained simply

WHERE tells SQL which rows to keep. If a row makes the condition true, it stays; otherwise, it’s filtered out.

SELECT columns
FROM table
WHERE condition;

Key idea: WHERE filters rows before aggregation. Typical execution order (simplified): FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Worked examples

Example 1 — Basic equality and date filter

Task: List active customers who signed up in 2024.

SELECT id, name, status, signup_date
FROM customers
WHERE status = 'active'
  AND signup_date >= '2024-01-01'
  AND signup_date < '2025-01-01';

Why: Inclusive 2024 using a half-open range avoids time-of-day issues.

Example 2 — Combining IN, BETWEEN, and NOT EQUAL

Task: US/CA orders between $100 and $500, non-cash only.

SELECT order_id, customer_id, total_amount, payment_method, order_date
FROM orders
WHERE country IN ('US','CA')
  AND total_amount BETWEEN 100 AND 500
  AND payment_method <> 'Cash';

Notes: BETWEEN is inclusive. <> means not equal.

Example 3 — NULLs and pattern matching

Task A: Orders without a coupon.

SELECT order_id
FROM orders
WHERE coupon_code IS NULL;

Task B: Products containing "Pro" (case-insensitive, portable).

SELECT product_id, product_name
FROM products
WHERE LOWER(product_name) LIKE '%pro%';

Notes: Use IS NULL (not = NULL). LOWER(...) LIKE ... is portable across SQL engines.

Build a filter (quick steps)

1. Write the plain-English rule (e.g., Active users in 2024, US only).
2. Map each part to operators (status='active', date range, country IN(...)).
3. Add parentheses when mixing AND and OR.
4. Run and spot-check a few rows for correctness.
5. Add/exclude edge cases (NULLs, time boundaries).

Exercises

Try these locally or in any SQL console. Solutions are available in the exercise cards and below in this page's Exercises section.

Exercise 1 — US signups in 2024

Table: customers(id, name, email, status, email_verified, country, created_at)

• Return: id, name, email, status, created_at
• Filter: country = 'US'
• Filter: created_at in calendar year 2024 (use a safe date boundary)
• Filter: status IN ('active','trial')
• Filter: email_verified = TRUE

Self-check:

• Did you avoid BETWEEN with an end-of-day time? Prefer half-open ranges.
• Did you use IN for multiple statuses?

Exercise 2 — Mid-value, no-coupon Q2 orders

Table: orders(order_id, customer_id, total_amount, payment_method, coupon_code, order_date)

• Return: order_id, customer_id, total_amount, order_date
• Filter: total_amount between 50 and 500 (inclusive)
• Filter: payment_method NOT IN ('Cash')
• Filter: coupon_code IS NULL
• Filter: order_date in Q2 2024 (Apr 1 inclusive to Jul 1 exclusive)

Self-check:

• Is your date range safe for timestamps?
• Did you use IS NULL, not = NULL?

Common mistakes & self-check

• Comparing NULL with = or <> — always use IS NULL / IS NOT NULL.
• Wrong precedence with AND/OR — use parentheses to make intent explicit.
• Time-boundaries missed — prefer half-open ranges (date >= start AND date < next_day/next_month).
• Case sensitivity with LIKE — use LOWER(col) LIKE 'pattern'.
• Filtering after aggregation — use HAVING for conditions on aggregates.
• String vs numeric comparisons — ensure literals are typed correctly ('100' vs 100).

Practical projects

• Activation funnel snapshot: From signups, filter users who verified email within 7 days and created at least one order.
• Refund review: Orders in the last 90 days with amount >= 200 and refund_requested = TRUE but refund_issued IS NULL.
• Churn watchlist: Customers with last_login < current_date - interval '30 days' AND plan IN ('pro','business').

Who this is for

• Business Analysts and aspiring analysts who need to slice data accurately.
• Anyone building reports, cohorts, or QA checks from raw tables.

Prerequisites

• Basic SELECT and FROM.
• Know common data types (text, number, date/datetime, boolean).
• Comfort reading simple schemas and column names.

Learning path

• Before: SELECT basics → Understanding data types.
• Now: WHERE filtering (this lesson).
• Next: ORDER BY, LIMIT; then GROUP BY with HAVING; then JOINs to filter across tables.

Mini challenge

Write a query to list id, email from customers who:

• country IN ('US','GB','CA')
• (status = 'active' OR (status = 'trial' AND created_at >= '2024-12-01'))
• email_verified = TRUE
• unsubscribed_at IS NULL

SELECT id, email
FROM customers
WHERE country IN ('US','GB','CA')
  AND (
        status = 'active'
        OR (status = 'trial' AND created_at >= '2024-12-01')
      )
  AND email_verified = TRUE
  AND unsubscribed_at IS NULL;

Next steps

• Refactor one real query you use: add parentheses and safe date ranges.
• Build a saved snippet library of common filters (date windows, null checks).
• Take the Quick Test below to confirm mastery. Available to everyone; only logged-in users get saved progress.