A single WHERE clause rarely asks a simple question. Real reports need combinations: employees in Engineering or Research, hired between 2019 and 2021, with a salary in a specific band, whose role contains the word 'Lead.' Building that filter without mistakes means mastering precedence, parentheses, and the specialized operators IN, BETWEEN, and LIKE. In this lesson you will move beyond one-column filters and write conditions that express real business rules — then learn how a single missing parenthesis can turn a precise query into a data leak.
flowchart LR E["expression"] --> A["AND first"] --> O["OR second"] style A fill:#0e7490,color:#fff
IN and BETWEEN: cleaner comparisons
When you need to match a column against several values, IN is cleaner than a chain of ORs. WHERE department_id IN (1, 2, 3) is shorter and harder to misread than three equality tests joined by OR. It also handles NULL more predictably than a long OR chain, though IN with a NULL in the list still returns no rows because NULL comparisons are unknown.
BETWEEN defines a closed range: WHERE salary BETWEEN 500000 AND 700000 includes both endpoints. It is shorthand for salary >= 500000 AND salary <= 700000, and it reads more naturally. One caution: BETWEEN works for text ranges too, so BETWEEN 'A' AND 'C' includes 'B' but not 'CZ' — the comparison is alphabetical, not length-based.
SELECT name, role, salary, hired_on FROM employees WHERE department_id IN (1, 2, 5) AND salary BETWEEN 500000 AND 800000 AND hired_on BETWEEN '2019-01-01' AND '2021-12-31';
Mixing AND, OR, and NOT
AND and OR let you combine conditions, but they do not play fair with each other. AND binds tighter than OR, which means it is evaluated first. WHERE role = 'Engineer' OR role = 'Senior Engineer' AND salary > 700000 does NOT mean 'engineers or senior engineers who earn over 700000' — it means 'every engineer, plus any senior engineer who earns over 700000.' The fix is parentheses.
NOT has even higher precedence than AND. WHERE NOT role = 'Engineer' OR salary > 700000 is read as (NOT role = 'Engineer') OR salary > 700000. If you meant to negate the entire combination, you must parenthesise: NOT (role = 'Engineer' OR salary > 700000). Parentheses cost nothing and save hours of debugging.
flowchart TD W1["Without parentheses\nEngineer OR Senior AND salary>700k"] --> R1["All Engineers + rich Seniors"] W2["With parentheses\n(Engineer OR Senior) AND salary>700k"] --> R2["Only rich Engineers and Seniors"] style R2 fill:#0e7490,color:#fff style R1 fill:#b45309,color:#fff
SELECT name, role, salary FROM employees WHERE (role = 'Engineer' OR role = 'Senior Engineer') AND salary > 700000;
LIKE and wildcards in complex filters
LIKE is already familiar for simple searches, but it becomes more powerful when combined with AND and OR. WHERE (name LIKE 'A%' OR name LIKE 'B%') AND salary > 500000 finds every employee whose name starts with A or B and who also earns above the threshold. The parentheses around the OR are essential — without them, AND would bind to the second LIKE only, and the first LIKE would stand alone, returning every name starting with A regardless of salary.
The wildcard _ matches exactly one character. WHERE name LIKE '_a%' finds every name whose second letter is a. This is useful for pattern matching where the length matters, such as product codes or ticket numbers with a fixed prefix length. Remember that LIKE is case-insensitive in SQLite but case-sensitive in most other databases, so portable code usually normalises case with UPPER or LOWER before comparing. Always test LIKE patterns on a small sample before trusting them on a full table; a misplaced % can match far more than you intended.
flowchart LR
C{"city = NULL?"} -->|no| D["Dropped"]
C -->|unknown| D
S{"city IS NULL?"} -->|yes| K["Kept"]
style K fill:#0e7490,color:#fff
style D fill:#b45309,color:#fff
SELECT name, role, department_id FROM employees WHERE (department_id IS NULL OR department_id = 5) AND salary > 500000;
NULL in complex conditions
NULL demands special attention whenever it appears in a filter. You already know that = NULL never matches and that IS NULL is the correct test. In a complex query, that test must be combined with the other conditions using the same precedence rules. WHERE department_id != 4 OR department_id IS NULL means 'anyone who is not in Support, plus anyone with no department.' Swap the OR for an AND and the meaning changes dramatically — you would be asking for people who simultaneously have a department that is not 4 and have no department at all, which is impossible. When a column can be NULL, always think about whether the missing rows should be included or excluded, and write the condition explicitly.
Practice: building precise filters
The exercises below start with IN and BETWEEN, move into combined AND/OR logic, then force you to repair a query broken by missing parentheses. The transfer task asks you to combine five different operators in one WHERE clause to match a realistic hiring brief — something no earlier snippet showed verbatim.
List the name and role of every employee whose department_id is 1, 3, or 5. Use IN instead of multiple OR conditions.
SELECT name, role FROM employees WHERE department_id -- use IN here ;
Use IN (1, 3, 5) to match any of the three values.
IN is cleaner than three OR conditions joined together.
SELECT name, role FROM employees WHERE department_id IN (1, 3, 5);
Find employees whose name starts with A or B and who earn more than 500000. Return name, role, and salary. Use parentheses to group the OR conditions correctly.
SELECT name, role, salary FROM employees WHERE -- group the two LIKE conditions with parentheses, then add the salary filter ;
Group the two LIKE conditions with parentheses.
Then add AND salary > 500000 outside the parentheses.
SELECT name, role, salary FROM employees WHERE (name LIKE 'A%' OR name LIKE 'B%') AND salary > 500000;
This query is meant to find employees in department 1 or 2 who earn between 400000 and 600000. Because of missing parentheses around the OR, it returns every employee in department 2 regardless of salary. Fix it.
SELECT name, department_id, salary FROM employees WHERE salary BETWEEN 400000 AND 600000 AND department_id = 1 OR department_id = 2 ;
AND binds tighter than OR, so the OR needs parentheses.
Wrap department_id = 1 OR department_id = 2 in parentheses.
SELECT name, department_id, salary FROM employees WHERE salary BETWEEN 400000 AND 600000 AND (department_id = 1 OR department_id = 2);
Find every employee who was hired between 2020 and 2022, is not in the Support department (id 4), and either earns less than 550000 or has a NULL department_id. Return name, salary, hired_on, and department_id. Use BETWEEN, a department filter, and parentheses around the OR.
SELECT name, salary, hired_on, department_id FROM employees WHERE -- build the complex condition here ;
Use hired_on BETWEEN '2020-01-01' AND '2022-12-31' for the date range.
Wrap the department filter and the salary/NULL filter each in their own parentheses.
Remember that != 4 alone excludes NULL department_ids.
SELECT name, salary, hired_on, department_id FROM employees WHERE hired_on BETWEEN '2020-01-01' AND '2022-12-31' AND (department_id != 4 OR department_id IS NULL) AND (salary < 550000 OR department_id IS NULL);
What is the result of WHERE x NOT IN (1, 2, NULL) for any value of x?
NOT IN compares x against each value. When the list contains NULL, the comparison x = NULL returns UNKNOWN. Since NOT IN requires every comparison to be FALSE, and UNKNOWN is not FALSE, the result is UNKNOWN. WHERE only keeps TRUE rows, so no rows are returned. This is one of SQL's most dangerous silent failures.
Recap
- IN matches a column against a list; BETWEEN defines a closed range including both endpoints.
- AND binds tighter than OR; NOT binds tighter than both. Use parentheses whenever you mix them.
- LIKE uses % for any sequence and _ for one character; test patterns on a sample first.
- NULL comparisons require IS NULL or IS NOT NULL; = NULL and != NULL silently fail.
- Combine filters deliberately: state the business rule in English, then translate it into conditions grouped with parentheses.
Next you will return to window functions to rank rows and compare each row to its neighbours.