WHERE Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

Syntax

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

Common Operators

The LIKE Operator

LIKE is used for pattern matching with two wildcards:

• % — matches zero or more characters
• _ — matches exactly one character

-- Names starting with 'A'
SELECT * FROM employees WHERE name LIKE 'A%';

-- Names with exactly 5 characters
SELECT * FROM employees WHERE name LIKE '_____';

NULL Handling

NULL represents an unknown or missing value. You cannot use = or != with NULL because any comparison with NULL evaluates to UNKNOWN (neither true nor false). Use IS NULL or IS NOT NULL instead.

SELECT * FROM employees WHERE email IS NULL;

Operator Precedence

When combining AND and OR, AND is evaluated first. Use parentheses to make your intent explicit:

-- Finds employees in dept 1 who earn > 50k OR any employee in dept 2
SELECT * FROM employees WHERE department_id = 1 AND salary > 50000 OR department_id = 2;

-- Same logic with parentheses for clarity
SELECT * FROM employees WHERE (department_id = 1 AND salary > 50000) OR department_id = 2;

WHERE with JOINs

The WHERE clause filters rows before grouping but after joins. If you filter on a column from the right table of a LEFT JOIN, you may accidentally turn it into an INNER JOIN:

-- This filters out users without orders (converts LEFT JOIN to INNER JOIN implicitly)
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

-- Correct: move the condition into the JOIN
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.amount > 100;

SELECT * FROM employees
WHERE salary > 50000;

SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 80000;

SELECT * FROM employees
WHERE name LIKE 'A%';

SELECT * FROM employees
WHERE email IS NULL;

SELECT * FROM employees
WHERE department_id IN (1, 2, 3);

SELECT * FROM employees
WHERE (department_id = 1 OR department_id = 2) AND salary > 50000;

SELECT * FROM employees
WHERE name LIKE '____';