LIKE, IN, BETWEEN and IS NULL

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

% matches any sequence of characters. _ matches exactly one character. So LIKE "_a%" means: any single character, then "a", then anything.

SELECT * FROM employees
WHERE email LIKE '%gmail%';

Placing % on both sides searches anywhere in the string. LIKE is not case-sensitive in MySQL by default but is in PostgreSQL.

SELECT * FROM employees
WHERE department IN ('Sales', 'HR', 'Finance');

IN is cleaner than writing multiple OR conditions. NOT IN excludes those values. Be careful: NOT IN returns no rows if any value in the list is NULL.

SELECT * FROM employees
WHERE salary BETWEEN 30000 AND 60000;

BETWEEN is inclusive — it includes both boundary values. Equivalent to: WHERE salary >= 30000 AND salary <= 60000.

SELECT * FROM employees
WHERE manager_id IS NULL;

Never use = NULL. NULL means unknown, so NULL = NULL is not true — it is also NULL. Always use IS NULL or IS NOT NULL.

SELECT * FROM employees
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

BETWEEN works on dates too. Always use ISO format (YYYY-MM-DD) for dates in SQL to avoid regional format confusion.