Filter Rows with WHERE

Ever run a query and get back 10,000 rows when you only needed 12? That's the pain of unfiltered data — slow reads, noisy results, and debugging sessions that spiral. In this lesson, you'll learn to filter rows using WHERE conditions in PostgreSQL, turning blunt table scans into surgical queries. By the end, you'll write precise conditions using comparison operators, logical keywords, and pattern matching — and know what to do when things go sideways.

The problem this lesson solves

Databases are built to hold a lot of data, but returning everything is rarely what you need. Without filtering, every SELECT pulls the entire table, which wastes bandwidth, slows down your application, and makes it impossible to spot the record you actually care about. Worse, unfiltered queries can mask logical bugs (like duplicates or unexpected NULLs), and in high-traffic systems they degrade performance for everyone.

Consider a simple task: find all users who signed up last month. Without WHERE, you'd scan the whole users table and check dates in application code — brittle, slow, and plain wrong when hours cut off days. The WHERE clause solves this at the database level: it evaluates a condition for every row and returns only those that satisfy it. That's filtering rows using WHERE conditions.

Core concept / mental model

Think of a PostgreSQL table as a warehouse of boxes, each row a box with labeled fields. The WHERE clause is your filter sheet: you specify rules, and the database walks each box, compares its labels to your rules, and keeps only the boxes that match. Rows that don't meet the condition are skipped — they never make it into your result set.

Key terms

• WHERE clause: A keyword in a SQL statement that defines the filtering condition.
• Predicate: The conditional expression inside WHERE that evaluates to TRUE, FALSE, or NULL (we'll handle NULL later).
• Boolean logic: Combining predicates with AND, OR, NOT to express complex rules.
• Comparison operators: =, <>, <, >, <=, >= for numeric and string comparisons.
• Pattern matching: Using LIKE and ILIKE with wildcards to match partial strings.

Pro tip: A WHERE clause filters rows, not groups. If you're tempted to filter after grouping, you'll need HAVING — but that's a later lesson. For now, remember: WHERE goes right after FROM, before GROUP BY or ORDER BY.

How it works step by step

When PostgreSQL executes a query with a WHERE clause, it follows a logical order (which you can observe with EXPLAIN, but that's a future lesson):

1. Read the table: The database scans the table (or uses an index, if available) to access rows.
2. Evaluate the predicate: For each row, it computes the WHERE expression. If the result is TRUE, the row is kept; if FALSE or NULL, it's discarded.
3. Return matching rows: Only rows satisfying the condition are included in the result set.

Where can you use WHERE?

• SELECT: Filter rows from a table (the most common case).
• UPDATE: Specify which rows to change.
• DELETE: Specify which rows to remove.

In all three, the syntax is the same: ... WHERE condition.

Order of clauses matters

In a SELECT statement, the logical order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. The WHERE clause filters before grouping, so you can safely filter on raw columns even when you're also aggregating.

Hands-on walkthrough

Let's start with a sample table. Be sure to run these examples in your PostgreSQL environment (e.g., psql or pgAdmin).

Setup: create the products table

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price NUMERIC(10,2),
    in_stock BOOLEAN DEFAULT TRUE
);

INSERT INTO products (name, category, price, in_stock) VALUES
    ('Widget', 'Gadgets', 19.99, TRUE),
    ('Gadget', 'Gadgets', 29.99, FALSE),
    ('Super Widget', 'Gadgets', 49.99, TRUE),
    ('Gizmo', 'Tools', 9.99, TRUE),
    ('Hammer', 'Tools', 15.50, TRUE),
    ('Screwdriver', 'Tools', 7.25, FALSE);

Example 1: Filter with a basic comparison

SELECT name, price FROM products WHERE price < 20.00;

Expected output:

  name      | price
------------+-------
 Widget     | 19.99
 Gizmo      |  9.99
 Hammer     | 15.50
 Screwdriver|  7.25
(4 rows)

Example 2: Combine multiple conditions with AND and OR

SELECT name, category, price
FROM products
WHERE category = 'Gadgets' AND price > 20.00;

Expected output:

  name      | category | price
------------+----------+-------
 Gadget     | Gadgets  | 29.99
 Super Widget| Gadgets | 49.99
(2 rows)

Now try OR — get products that are either in the Tools category OR in stock:

SELECT name, category, in_stock
FROM products
WHERE category = 'Tools' OR in_stock = TRUE;

Expected output: All rows except 'Gadget' (because it's not in Tools and not in stock).

Example 3: Pattern matching with LIKE

SELECT name FROM products WHERE name LIKE 'Widget%';

Expected output:

  name
---------
 Widget
 Super Widget
(2 rows)

% matches zero or more characters. _ matches exactly one character.

Example 4: Filter with dates (common in real apps)

Create a simple orders table and filter by date range:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(id),
    qty INT,
    order_date DATE
);

INSERT INTO orders (product_id, qty, order_date) VALUES
    (1, 2, '2025-05-01'),
    (2, 1, '2025-05-02'),
    (3, 4, '2025-06-01'),
    (4, 1, '2025-06-10');

-- Find all orders from May 2025
SELECT * FROM orders WHERE order_date >= '2025-05-01' AND order_date < '2025-06-01';

Expected output: Two rows (the first two orders).

Pro tip: For date ranges, always use >= start AND < end+1 day instead of BETWEEN when you care about time components — it avoids missing records on the upper boundary.

Compare options / when to choose what

Different filtering tools in PostgreSQL serve different purposes. Here’s how they stack up:

When to choose what

• Use = for exact matches, but be aware of case sensitivity (use ILIKE or LOWER() for case-insensitive).
• Use AND to narrow results (more conditions = fewer rows).
• Use OR to broaden results, but beware of performance pitfalls if columns aren’t indexed.
• For ranges, BETWEEN is readable but inclusive; > AND < gives you more control over bounds.
• Never use = NULL — it always returns no rows. Always use IS NULL or IS NOT NULL.

Troubleshooting & edge cases

Even experienced developers hit these snags. Here’s how to fix them fast.

1. Results are empty when you expect rows

• Check for NULLs: comparisons with NULL produce NULL, which is neither TRUE nor FALSE. Use IS NULL or IS NOT NULL explicitly.

SELECT * FROM products WHERE in_stock = NULL;  -- returns 0 rows, always
SELECT * FROM products WHERE in_stock IS NULL; -- correct

2. OR caveat

If you mix AND and OR, parentheses are critical:

SELECT * FROM products WHERE category = 'Gadgets' OR category = 'Tools' AND price > 10;

Without parentheses, AND binds tighter than OR, so this returns all Gadgets (even cheap ones) plus expensive Tools. Add parentheses to get the intended result:

SELECT * FROM products WHERE (category = 'Gadgets' OR category = 'Tools') AND price > 10;

3. LIKE special characters

If your search term contains % or _, escape them with a backslash:

SELECT * FROM products WHERE name LIKE '100\%' ESCAPE '\';

4. Performance traps

Applying functions to columns (e.g., LOWER(name) = 'widget') prevents index usage. Instead, store lowercase or use ILIKE for case-insensitive search. Also, leading wildcards (LIKE '%widget') force full scans.

5. Date boundaries

BETWEEN is inclusive: order_date BETWEEN '2025-05-01' AND '2025-05-31' misses orders on May 31 at midnight. Use >= and < next day for correctness with times.

What you learned & what's next

You’ve now mastered filter rows using WHERE conditions — the core skill for precise data retrieval. You can apply comparison operators, combine logic, use pattern matching, and handle NULLs safely. You’re also aware of performance pitfalls and common mistakes.

Your next lesson is likely sorting and limiting results (ORDER BY and LIMIT) to control output order and size — a natural next step after filtering. Or jump into aggregations with GROUP BY to summarize filtered datasets. Either way, you have the foundation to build more complex queries.

Keep practicing: try filtering the products table with your own conditions, experiment with IN and BETWEEN, and don’t forget to test NULL behavior. You’re well on your way to querying with precision.