Filtering Data with WHERE

The WHERE clause filters which rows a SELECT, UPDATE, or DELETE statement affects, evaluating a boolean condition against each row and keeping only the rows where it evaluates to true. Precise filtering is one of the most frequently used SQL skills, and combining conditions correctly is essential to getting the right result set.

Why it matters

A table may hold millions of rows, and a query is useful only when it returns the right ones. The WHERE clause does that work. Its traps, especially how NULL behaves and how AND and OR combine, produce queries that run without error and quietly return the wrong rows.

Comparison Operators

Standard comparison operators — = != (or <>) > < >= <=</code> — compare a column against a value or another column. Comparing against NULL with = never returns true; NULL comparisons require the special IS NULL or IS NOT NULL operators instead.

Combining Conditions: AND, OR, IN, BETWEEN

Multiple conditions combine with AND (all must be true) and OR (at least one must be true); parentheses control precedence when mixing them. IN (value1, value2, ...) checks membership in a list, replacing a long chain of OR comparisons on the same column. BETWEEN low AND high checks an inclusive range, equivalent to column >= low AND column <= high</code>.

Pattern Matching with LIKE

LIKE performs simple pattern matching on text using two wildcards: % matches any sequence of characters (including none), and _ matches exactly one character. For example, name LIKE 'A%' matches any name starting with "A," and code LIKE 'A_1' matches a three-character code starting with "A" and ending with "1."

Examples

SELECT name, department, salary
FROM employees
WHERE department IN ('Engineering', 'Sales')
  AND salary BETWEEN 50000 AND 90000
  AND name LIKE 'A%';

Returns 1 row: ('Asha Rao', 'Engineering', 72000)

SELECT id, name, department
FROM employees
WHERE department = 'Sales' OR department IS NULL;

Returns 2 rows: (2, 'Rohit Sharma', 'Sales'), (5, 'Meera Iyer', NULL)

Exercise

Write two queries. First: the names of employees in the IT or HR department who earn more than 40000 and whose name starts with A. Second: the names of employees who have no manager.

Expected output: name, Asha, name, Zoya (one per line)

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees (id, name, department, salary, manager_id) VALUES
  (1, 'Asha', 'IT', 50000, 3),
  (2, 'Amit', 'Sales', 60000, 3),
  (3, 'Zoya', 'HR', 70000, NULL),
  (4, 'Anil', 'IT', 35000, 1);

-- TODO: IT or HR, salary above 40000, name starting with A

-- TODO: employees with no manager

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees (id, name, department, salary, manager_id) VALUES
  (1, 'Asha', 'IT', 50000, 3),
  (2, 'Amit', 'Sales', 60000, 3),
  (3, 'Zoya', 'HR', 70000, NULL),
  (4, 'Anil', 'IT', 35000, 1);

SELECT name
FROM employees
WHERE department IN ('IT', 'HR')
  AND salary > 40000
  AND name LIKE 'A%';

SELECT name FROM employees WHERE manager_id IS NULL;

Quick quiz

Interview questions