WHERE clause

The WHERE clause filters rows in a SQL query. Attached to a SELECT statement, UPDATE, or DELETE, it specifies a condition that each row must satisfy to be included in the result or affected by the operation. Without a WHERE clause, a SELECT returns every row and an UPDATE or DELETE operates on every row in the table.

SELECT * FROM employees
WHERE first_name = 'Khalid';

The condition can combine multiple subconditions with AND, OR, and NOT:

SELECT * FROM employees
WHERE first_name = 'Khalid' OR first_name = 'Maris';
 
SELECT * FROM employees
WHERE hire_date > '2020-01-01' AND salary > 80000;

For pattern matching on strings, use the LIKE operator:

SELECT * FROM employees WHERE first_name LIKE 'Mo%';

For checking against a list of values, IN:

SELECT * FROM employees WHERE department IN ('Engineering', 'Sales', 'Marketing');

For null-handling, IS NULL and IS NOT NULL. (Comparing with = NULL doesn’t work — NULL = NULL is itself NULL, not true.)

SELECT * FROM students WHERE major_code IS NULL;

For range checks, BETWEEN:

SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;

The WHERE clause is the single most common reason a SQL query is slow. The database has to evaluate the condition on every row it considers, and if the condition can’t use an index, that means a full table scan. Building the right indexes — and writing conditions in a way the optimizer can use them — is much of the art of SQL performance tuning.