WHERE Clause — SQL Tutorial with Examples

What You'll Learn

WHERE filters rows before aggregation. It supports operators: =, !=, <, >, <=, >=, LIKE, IN, BETWEEN, IS NULL, IS NOT NULL. Combine conditions with AND (both must be true), OR (either must be true), NOT (inverts condition).

Syntax

SELECT cols FROM table WHERE condition;
SELECT cols FROM table WHERE c1 AND c2;
SELECT cols FROM table WHERE col IN (v1, v2);
SELECT cols FROM table WHERE col BETWEEN x AND y;

Example

-- AND: both conditions must hold
SELECT name, salary, department_id
FROM employees
WHERE salary > 80000 AND department_id = 1;   -- 1 = Engineering

-- IN: any of the listed values
SELECT name, department_id FROM employees
WHERE department_id IN (1, 2, 3);

-- BETWEEN: inclusive range on hire dates
SELECT name, hire_date FROM employees
WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

-- LIKE: names starting with A
SELECT name FROM employees WHERE name LIKE 'A%';

Beyond the Basics

Data in play — reference tables for this topic

-- employees (from Topic 1)
-- id | name    | department_id | salary | hire_date
-- 1  | Ada     | 1             | 145000 | 2021-03-01
-- 2  | Grace   | 1             | 115000 | 2022-06-13
-- 3  | Alan    | 2             | 92000  | 2020-11-02
-- 4  | Edsger  | NULL          | 78000  | 2023-08-19
-- 5  | Barbara | 3             | 64000  | 2024-01-08

-- departments (from Topic 1)
-- 1 = Engineering, 2 = Sales, 3 = HR

AND binds tighter than OR — the precedence bug

AND is evaluated before OR, so writing conditions in reading order silently changes their meaning. This is one of the most common logic bugs in real codebases — and it never throws an error; it just returns the wrong rows.

-- INTENDED: (Engineering OR Sales) AND salary > 100000
-- WRITTEN:   Engineering OR (Sales AND salary > 100000)
SELECT name, department_id, salary
FROM employees
WHERE department_id = 1 OR department_id = 2 AND salary > 100000;
-- ^ returns ALL of Engineering, regardless of salary

-- Explicit parentheses make the intent executable:
SELECT name, department_id, salary
FROM employees
WHERE (department_id = 1 OR department_id = 2) AND salary > 100000;

Parentheses are free. Add them whenever AND and OR share a WHERE clause — even when you're sure they aren't needed.

Three-valued logic starts here

Every predicate returns TRUE, FALSE — or UNKNOWN, when NULL is involved. UNKNOWN is not FALSE: rows that evaluate to UNKNOWN are silently excluded by WHERE, from BOTH the positive and the negative form of the same condition.

-- Returns 4 rows — Edsger (department_id = NULL) is silently missing:
SELECT name FROM employees WHERE department_id != 3;

-- He is not in the != 3 result... but not in this either:
SELECT name FROM employees WHERE department_id = 3;

-- Include the unknowns explicitly:
SELECT name FROM employees
WHERE department_id != 3 OR department_id IS NULL;

Whenever a filterable column is nullable, ask 'where do the NULL rows go?' before shipping. Topic 7 covers the full toolkit.

Sargable filters: keep the column naked

Wrapping a column in a function — YEAR(hire_date) = 2022 — forces the engine to call that function on every row, making any index on the column unusable. A 'sargable' (search-argument-friendly) rewrite of the same logic can use the index.

-- Not sargable: function applied to the column
SELECT * FROM employees
WHERE YEAR(hire_date) = 2022;

-- Sargable: same meaning, index-usable range condition
SELECT * FROM employees
WHERE hire_date >= '2022-01-01' AND hire_date < '2023-01-01';

Put functions on constants, not on columns. This one habit is half of query tuning before you even reach Topics 21-22.

Common Mistakes

Using '= NULL' instead of 'IS NULL'. NULL comparisons with = always return UNKNOWN (not TRUE), so rows are never matched. Also, LIKE '%text' cannot use indexes — prefer 'text%' for performance.

Interview Tips

BETWEEN is inclusive on both ends. Know that OR conditions can prevent index usage — sometimes rewriting as UNION of two queries is faster. Explain precedence: AND is evaluated before OR.

Official References

Test Yourself — 6 questions

Self-check · 0/6 answered

1. WHERE department_id = 1 OR department_id = 2 AND salary > 100000 — what does this actually return?

2. What is the fix when AND and OR share a WHERE clause?

3. Edsger's department_id is NULL. Does WHERE department_id != 3 return him?

4. How do you correctly find rows where a column has no value?

5. Why is WHERE YEAR(hire_date) = 2022 slower than a date range condition?

6. Is BETWEEN '2022-01-01' AND '2022-12-31' inclusive?

Practice

Return the name and salary of employees earning more than 60,000.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Why doesn't WHERE department_id = NULL match anything?

Because = NULL evaluates to UNKNOWN, never TRUE, and WHERE only keeps TRUE rows. NULL means 'unknown value', and unknown is not equal to unknown. The test for absence is IS NULL — a special operator precisely because = cannot do the job.

Is BETWEEN inclusive?

Yes — on both ends. WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31' includes midnight of Jan 1 but excludes most of Dec 31 if the column has a time component. For timestamps, prefer >= start AND < next_period_start.