Filtering Data

Filtering Data with WHERE

Most real queries don’t want every row in a table — they want a specific subset. The WHERE clause is how you express that condition. Every condition in WHERE must evaluate to TRUE for a row to be returned; rows where the condition is FALSE or NULL are silently excluded. Getting comfortable with WHERE is the foundation of writing useful SQL.

Comparison Operators

The six comparison operators work on numbers, dates, and text. They let you express the most common filtering needs — equality, ordering, and exclusion — in a form that maps directly to how you’d describe the filter in plain language.

-- Greater than: employees earning above a threshold
SELECT name, salary
FROM employees
WHERE salary > 60000;

-- Greater than or equal: employees hired from a specific date onward
SELECT name, hire_date
FROM employees
WHERE hire_date >= '2022-01-01';

-- Not equal: everyone outside a specific department
SELECT name, department
FROM employees
WHERE department <> 'Sales';  -- <> means "not equal", same as !=

Logical Operators: AND, OR, NOT

Real filters rarely have just one condition. AND, OR, and NOT let you combine conditions — AND requires both sides to be true, OR requires either side to be true, and NOT inverts a condition. The ability to combine these freely is what makes WHERE so expressive.

-- AND: both conditions required — high earners in a specific department
SELECT name, salary, department
FROM employees
WHERE department = 'Engineering'
  AND salary >= 80000;

-- OR: either condition is enough — anyone in Marketing or Sales
SELECT name, department
FROM employees
WHERE department = 'Marketing'
   OR department = 'Sales';

-- NOT: inverts a condition — everything except cancelled orders
SELECT name, status
FROM orders
WHERE NOT status = 'cancelled';

Parentheses Control Evaluation Order

AND binds more tightly than OR, so mixing them without parentheses produces results that look wrong but are syntactically valid. This is one of the most common sources of subtle bugs in SQL. Always use parentheses when combining AND and OR in the same condition.

-- Without parentheses: AND evaluated first, then OR
-- This gets ALL Engineers, plus Marketers earning over 70k
-- (probably not what you wanted)
SELECT name, department, salary
FROM employees
WHERE department = 'Engineering'
   OR department = 'Marketing'
  AND salary > 70000;

-- With parentheses: explicit grouping makes intent clear
-- Gets Engineers OR Marketers, but only those earning over 70k
SELECT name, department, salary
FROM employees
WHERE (department = 'Engineering' OR department = 'Marketing')
  AND salary > 70000;

BETWEEN … AND

Range checks appear constantly in SQL — date ranges, price ranges, age brackets. BETWEEN is shorthand for >= lower AND <= upper that makes the intent more readable at a glance. Both endpoints are inclusive, which is worth remembering when working with dates.

-- Salary in a range (both endpoints included)
SELECT name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 80000;

-- Date ranges work the same way
SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

IN and NOT IN

When you need to check whether a value matches any item in a list, IN is cleaner than chaining multiple OR conditions. It’s easier to read, and many query planners can optimize it more effectively than a long OR chain.

-- Equivalent to: department = 'HR' OR department = 'Legal' OR department = 'Finance'
SELECT name, department
FROM employees
WHERE department IN ('HR', 'Legal', 'Finance');

-- NOT IN excludes a set of values
SELECT name, status
FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

Be careful with NOT IN when the list might contain a NULL. If any value in the list is NULL, NOT IN returns no rows because NULL comparisons are always unknown.

LIKE and ILIKE with Wildcards

Pattern matching is essential when you’re dealing with user-entered text, product codes, or any data where you need to match on partial strings. LIKE gives you two wildcard characters that cover the most common patterns — prefix, suffix, and substring matches.

LIKE matches a pattern using two wildcard characters:

• % matches any sequence of zero or more characters
• _ matches exactly one character

-- Names starting with 'A' — prefix match
SELECT name FROM employees WHERE name LIKE 'A%';

-- Names ending with 'son' — suffix match
SELECT name FROM employees WHERE name LIKE '%son';

-- Names with exactly 5 characters — fixed-length match
SELECT name FROM employees WHERE name LIKE '_____';

-- Names containing 'an' anywhere — substring match
SELECT name FROM employees WHERE name LIKE '%an%';

ILIKE does the same but ignores case — a PostgreSQL-specific extension that’s invaluable for searching user-entered strings:

-- Matches 'alice', 'Alice', 'ALICE', 'aLiCe', etc.
SELECT name FROM employees WHERE name ILIKE 'alice%';

IS NULL and IS NOT NULL

NULL represents an unknown or missing value — not zero, not an empty string, but the complete absence of information. Because of this, SQL can’t compare NULL with = or <>: the result of any comparison involving NULL is itself NULL, which is treated as false in WHERE. This trips up almost every SQL beginner at least once.

-- WRONG: this silently returns no rows, even if NULLs exist
SELECT name FROM employees WHERE manager_id = NULL;

-- CORRECT: use IS NULL to test for missing values
SELECT name FROM employees WHERE manager_id IS NULL;

-- Find employees who do have a manager assigned
SELECT name FROM employees WHERE manager_id IS NOT NULL;

COALESCE and NULLIF

NULL values often need to be handled gracefully in output — you want to display a fallback value rather than a blank. COALESCE solves this by returning the first non-null value from its argument list. It’s one of the most-used functions in real-world SQL because optional data is everywhere.

-- Display a fallback label instead of NULL in results
SELECT name, COALESCE(department, 'No department') AS department
FROM employees;

-- Check multiple fallback columns in order
SELECT name, COALESCE(mobile_phone, work_phone, 'No phone on file') AS contact
FROM employees;

NULLIF is the inverse: it returns NULL when two expressions are equal, and returns the first expression otherwise. Its most common use is guarding against division by zero, which would otherwise crash a query entirely.

-- Returns NULL instead of a division-by-zero error when total_visits = 0
SELECT page, conversions / NULLIF(total_visits, 0) AS conversion_rate
FROM page_stats;

Putting It All Together

Real queries combine multiple filtering techniques. Here’s a query that filters by date range, membership in a list, non-null status, and a case-insensitive pattern — all in one WHERE clause.

SELECT
    e.name,
    e.department,
    COALESCE(e.bonus, 0) AS bonus  -- show 0 instead of NULL for display
FROM employees e
WHERE e.hire_date BETWEEN '2020-01-01' AND '2023-12-31'  -- date range
  AND e.department IN ('Engineering', 'Product', 'Design') -- membership check
  AND e.status IS NOT NULL                                 -- exclude missing status
  AND e.name ILIKE '%smith%'                              -- case-insensitive search
ORDER BY e.hire_date;

Filtering is one of the most frequently used parts of SQL. Getting comfortable with IS NULL, COALESCE, and the behavior of NULL in general will save you hours of debugging unexpected empty result sets.