Loading…
Loading…
SQL tutorial · Beginner
⏱️ 10 mins read
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).
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;-- 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%';| 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 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;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;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';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.
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.
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?
Return the name and salary of employees earning more than 60,000.
⚡ Solve it in the SQL playground →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.
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.