SQL Where

The WHERE clause is how you tell SQL which rows you actually want. Without it, a query returns every row in a table; with it, the database engine tests each row against a condition and keeps only the ones that pass. It’s one of the first clauses every SQL learner needs, and it underlies almost every real-world query, from simple lookups to complex reporting.

Overview / How WHERE Works

WHERE is a row-filtering clause used in SELECT, UPDATE, and DELETE statements. It appears after FROM (and any JOINs) and before GROUP BY, HAVING, or ORDER BY. The database engine evaluates the WHERE condition once per row, using the row’s own column values, and the condition must reduce to TRUE, FALSE, or UNKNOWN (SQL’s three-valued logic). Only rows where the condition evaluates to TRUE are kept — rows that evaluate to FALSE or UNKNOWN are discarded.

Conceptually, you can imagine the engine scanning every row of the table one at a time and asking “does this row satisfy the condition?” In practice, a good query optimizer avoids scanning every row when it can. If a WHERE condition references a column that has an index (for example a primary key or a column with a CREATE INDEX statement on it), the engine can use that index to jump directly to matching rows instead of reading the whole table — this is called an index seek, versus a full table scan. Conditions built from simple equality or range comparisons on indexed columns are usually the fastest to filter on; conditions that wrap a column in a function (like WHERE UPPER(name) = 'ALICE') or use a leading wildcard in LIKE (like '%son') typically prevent the engine from using an index efficiently, forcing a full scan.

It’s important to understand that WHERE filters individual rows before any grouping happens. If you want to filter on the result of an aggregate function (like COUNT() or AVG()) applied to a group, you need HAVING instead — more on that in Common Mistakes below.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Part	Meaning
condition	A boolean expression evaluated per row, e.g. salary > 60000.
Comparison operators	=, != or <>, >, <, >=, <=
Logical operators	AND, OR, NOT — combine multiple conditions
BETWEEN a AND b	True if the value is within an inclusive range
IN (v1, v2, ...)	True if the value matches any item in a list
LIKE pattern	Pattern matching with % (any characters) and _ (one character)
IS NULL / IS NOT NULL	Tests for missing values — never use = NULL

Examples

Example 1: A simple equality filter

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    department TEXT,
    salary REAL,
    hire_date TEXT
);

INSERT INTO employees (id, name, department, salary, hire_date) VALUES
    (1, 'Alice Johnson', 'Engineering', 95000, '2019-03-15'),
    (2, 'Bob Smith', 'Sales', 62000, '2021-07-01'),
    (3, 'Carol Davis', 'Engineering', 88000, '2020-01-10'),
    (4, 'David Lee', 'Marketing', 54000, '2022-05-23');

SELECT * FROM employees WHERE department = 'Engineering';

Result:

id	name	department	salary	hire_date
1	Alice Johnson	Engineering	95000	2019-03-15
3	Carol Davis	Engineering	88000	2020-01-10

The engine checks every row’s department value against the literal string 'Engineering' and keeps only the two rows that match. Bob and David are excluded because their department is 'Sales' and 'Marketing' respectively.

Example 2: Combining conditions with AND

SELECT name, salary FROM employees
WHERE salary > 60000 AND department != 'Marketing';

Result:

name	salary
Alice Johnson	95000
Bob Smith	62000
Carol Davis	88000

Both conditions must be true for a row to survive. David Lee is excluded on two counts: his salary (54000) isn’t above 60000, and his department is Marketing. With AND, a row needs to pass every condition; with OR, passing just one condition is enough.

Example 3: IN, a date range, and ORDER BY

SELECT name, department, hire_date FROM employees
WHERE department IN ('Engineering', 'Marketing')
  AND hire_date >= '2020-01-01'
ORDER BY hire_date;

Result:

name	department	hire_date
Carol Davis	Engineering	2020-01-10
David Lee	Marketing	2022-05-23

IN is shorthand for a chain of OR-connected equality checks — here it’s equivalent to (department = 'Engineering' OR department = 'Marketing'). Because dates are stored as 'YYYY-MM-DD' text, they sort and compare correctly as strings. Alice Johnson is excluded even though she’s in Engineering, because her hire date (2019-03-15) is before the cutoff.

How It Works Step by Step (Under the Hood)

SQL is declarative — you describe what you want, not how to get it — but the engine still processes a query in a fixed logical order, regardless of the order you type the clauses in:

1. FROM — identify the source table(s) and perform any joins.
2. WHERE — evaluate the filter condition against each individual row and discard rows that don’t satisfy it.
3. GROUP BY — bucket the remaining rows into groups, if grouping is requested.
4. HAVING — filter those groups based on aggregate conditions.
5. SELECT — compute the requested columns/expressions for what’s left.
6. ORDER BY — sort the final result set.
7. LIMIT — trim to a requested number of rows.

Because WHERE runs at step 2, before any grouping or aggregation, it has no visibility into aggregate results and cannot reference column aliases defined in the SELECT list (those don’t exist yet). It also can’t reference window function results, since those are computed even later in the pipeline. This ordering is also why WHERE is generally more efficient than filtering after aggregation — it shrinks the row set as early as possible, before the more expensive grouping and sorting work happens.

Common Mistakes

Mistake 1: Using an aggregate function inside WHERE

CREATE TABLE employees (
    id INTEGER, name TEXT, department TEXT, salary REAL, hire_date TEXT
);
INSERT INTO employees (id, name, department, salary, hire_date) VALUES
    (1, 'Alice Johnson', 'Engineering', 95000, '2019-03-15'),
    (2, 'Bob Smith', 'Sales', 62000, '2021-07-01'),
    (3, 'Carol Davis', 'Engineering', 88000, '2020-01-10'),
    (4, 'David Lee', 'Marketing', 54000, '2022-05-23');

SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 70000
GROUP BY department;

This fails because WHERE runs before grouping/aggregation exists (step 2 above), so AVG(salary) has nothing to average yet — most databases, including SQLite, reject this with a “misuse of aggregate function” error. Aggregate conditions belong in HAVING, which runs after grouping:

CREATE TABLE employees (
    id INTEGER, name TEXT, department TEXT, salary REAL, hire_date TEXT
);
INSERT INTO employees (id, name, department, salary, hire_date) VALUES
    (1, 'Alice Johnson', 'Engineering', 95000, '2019-03-15'),
    (2, 'Bob Smith', 'Sales', 62000, '2021-07-01'),
    (3, 'Carol Davis', 'Engineering', 88000, '2020-01-10'),
    (4, 'David Lee', 'Marketing', 54000, '2022-05-23');

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;

Result: only Engineering | 91500.0 is returned — Sales averages 62000 and Marketing averages 54000, both below the 70000 threshold.

Mistake 2: Comparing to NULL with =

CREATE TABLE contacts (id INTEGER, name TEXT, phone TEXT);
INSERT INTO contacts (id, name, phone) VALUES
    (1, 'Amy', '555-0101'),
    (2, 'Ben', NULL),
    (3, 'Cara', '555-0103');

SELECT * FROM contacts WHERE phone = NULL;

Result: zero rows — even Ben, whose phone really is NULL. In SQL, NULL means “unknown,” and any comparison involving an unknown value (including NULL = NULL) evaluates to UNKNOWN, not TRUE, so the row is dropped. The fix is to use IS NULL, which is a dedicated null-check rather than a comparison:

CREATE TABLE contacts (id INTEGER, name TEXT, phone TEXT);
INSERT INTO contacts (id, name, phone) VALUES
    (1, 'Amy', '555-0101'),
    (2, 'Ben', NULL),
    (3, 'Cara', '555-0103');

SELECT * FROM contacts WHERE phone IS NULL;

Result: 2 | Ben | NULL — exactly the row we wanted.

Best Practices

• Use IS NULL / IS NOT NULL for missing values — never = NULL or != NULL.
• Put filter logic in WHERE, not HAVING, whenever it doesn’t depend on an aggregate — it lets the engine discard rows earlier and often use an index.
• Wrap conditions in parentheses when mixing AND and OR, since AND binds tighter than OR and mixing them without parentheses is a common source of subtle bugs.
• Avoid wrapping indexed columns in functions (e.g. WHERE UPPER(name) = 'ALICE') — it disables index usage; instead normalize data at insert time or use a functional/expression index if your database supports one.
• Prefer BETWEEN or explicit >=/< pairs for ranges instead of chains of OR — it’s clearer and easier for the optimizer to reason about.
• Use IN (...) instead of long OR chains when checking one column against many possible values.
• Be careful with LIKE patterns that start with % (e.g. '%smith') — they generally can’t use a standard index and force a full scan.

Practice Exercises

• Using the employees table from the examples above, write a query that returns the name and salary of everyone earning less than 90000 who is not in the Sales department.
• Write a query against employees that returns everyone hired in 2020 or later, ordered by salary descending. (Hint: compare hire_date as text against '2020-01-01'.)
• Given the contacts table from Mistake 2, write one query that returns everyone whose phone is not missing, sorted by name.

Summary

• WHERE filters individual rows based on a condition, and runs right after FROM/joins in the logical query order — before GROUP BY, HAVING, and ORDER BY.
• Rows are kept only when the condition evaluates to TRUE; FALSE and UNKNOWN (common with NULL) rows are dropped.
• Combine conditions with AND/OR/NOT, and use BETWEEN, IN, and LIKE for ranges, lists, and patterns.
• Use IS NULL/IS NOT NULL for null checks — = NULL silently returns no matches.
• Aggregate conditions (on COUNT, AVG, etc.) belong in HAVING, not WHERE, because aggregation happens after WHERE runs.
• Indexed, function-free, leading-wildcard-free conditions let the engine use an index seek instead of a full table scan, which matters a lot on large tables.