Chapter 12: Filtering Results

Filtering limits query results to only the rows that meet specified conditions. The WHERE clause forms the backbone of filtering, combining comparison operators, ranges, pattern matching, and logical connectors to express precise criteria. This chapter explores essential filters, logical combinations, and how operator precedence affects evaluation.

WHERE, BETWEEN, LIKE, IN, IS NULL

The WHERE clause specifies a condition for each row. Only rows evaluating to true are included in the result set.

Basic comparisons

SELECT * 
FROM employees
WHERE department = 'Sales'
  AND salary > 60000;

• = equal
• <> or != not equal
• >, <, >=, <= comparisons

Use single quotes for string literals and avoid implicit conversions between text and numbers.

Using BETWEEN for ranges

SELECT id, total
FROM orders
WHERE total BETWEEN 100 AND 500;

BETWEEN includes both boundary values. For exclusive ranges, use explicit comparisons.

WHERE total > 100 AND total < 500;

Pattern matching with LIKE

LIKE uses wildcards to match string patterns:

• % matches any sequence of characters.
• _ matches a single character.

SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'Sm%';     -- starts with Sm

SELECT * 
FROM customers
WHERE email LIKE '%@example.com';  -- ends with @example.com

Matching sets with IN

SELECT * 
FROM products
WHERE category IN ('Electronics', 'Books', 'Home');

IN checks membership in a list of values or subquery results:

SELECT id, name
FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE region = 'EU'
);

Checking for NULL values

NULL represents missing or unknown data. You must use IS NULL or IS NOT NULL to test it.

SELECT id, email
FROM customers
WHERE email IS NULL;

SELECT id, email
FROM customers
WHERE email IS NOT NULL;

Logical operators

Logical operators combine multiple conditions in a WHERE clause. They control how filters interact to include or exclude rows.

AND and OR

SELECT * 
FROM employees
WHERE department = 'Engineering'
  AND salary > 90000;

All conditions joined by AND must be true for a row to qualify. OR allows any one of its conditions to be true.

SELECT * 
FROM employees
WHERE department = 'Sales'
   OR department = 'Marketing';

You can mix them, but operator precedence determines evaluation order (see below). Parentheses clarify intent and prevent logical errors.

NOT operator

NOT negates a condition. It can be used with any expression that evaluates to true or false.

SELECT * 
FROM employees
WHERE NOT (department = 'HR' OR salary < 50000);

Combining filters logically

SELECT * 
FROM orders
WHERE (status = 'SHIPPED' OR status = 'DELIVERED')
  AND total > 100;

Always test combinations carefully. A missing parenthesis or inverted logic can drastically change results.

Operator precedence and parentheses

When combining multiple conditions, SQL follows a fixed order of evaluation:

Without parentheses, AND conditions are evaluated before OR. This means:

-- This query returns rows where
-- (department = 'Sales' AND salary > 50000) OR department = 'HR'
SELECT * 
FROM employees
WHERE department = 'Sales' AND salary > 50000
   OR department = 'HR';

To make intent explicit, group conditions with parentheses:

SELECT * 
FROM employees
WHERE department = 'HR'
   OR (department = 'Sales' AND salary > 50000);

Mastering filters and logical combinations makes queries accurate, readable, and efficient. By understanding how SQL evaluates conditions, you can write powerful searches that return exactly the intended data.