Filtering & Conditions

Filtering is how you tell the database "I only want rows that meet these criteria". The WHERE clause supports a rich set of operators for every type of comparison. This chapter covers them all, explains common pitfalls, and shows how to combine filters intelligently.

We will continue using the same employees table:

AND, OR, NOT — Combining Conditions

Real filters usually combine multiple conditions. SQL provides three logical operators for this.

AND — both conditions must be true

-- Only IT employees earning more than 85000
SELECT name, dept, salary
FROM   employees
WHERE  dept = 'IT'
AND    salary > 85000;

Result:

OR — at least one condition must be true

-- HR or Finance employees (either department)
SELECT name, dept
FROM   employees
WHERE  dept = 'HR'
OR     dept = 'Finance';

Result:

NOT — reverses a condition

-- Everyone except IT employees
SELECT name, dept
FROM   employees
WHERE  NOT dept = 'IT';

-- This is equivalent to:
WHERE  dept <> 'IT'

Operator Precedence — The AND/OR Trap

This is one of the most common SQL bugs. AND has higher precedence than OR, just like multiplication has higher precedence than addition in maths. This means AND conditions are evaluated first, then OR conditions.

-- DANGEROUS: what does this actually filter?
SELECT name, dept, salary
FROM   employees
WHERE  dept = 'HR'
OR     dept = 'Finance'
AND    salary > 70000;

Because AND binds tighter, this is evaluated as:

dept = 'HR'  OR  (dept = 'Finance' AND salary > 70000)

Which means: all HR employees PLUS Finance employees earning above 70k.

That is probably not what was intended. The fix is always use parentheses when mixing AND and OR:

-- CORRECT: parentheses make the intent crystal clear
SELECT name, dept, salary
FROM   employees
WHERE  (dept = 'HR' OR dept = 'Finance')
AND    salary > 70000;

Now it means: (HR or Finance) AND salary above 70000 — two departments, filtered by salary.

BETWEEN — Range Testing

BETWEEN checks whether a value falls within a range. It is inclusive — meaning both the lower and upper bounds are included.

-- Salaries from 70000 to 90000 (includes 70000 and 90000 exactly)
SELECT name, salary
FROM   employees
WHERE  salary BETWEEN 70000 AND 90000;

Result:

BETWEEN 70000 AND 90000 is exactly equivalent to salary >= 70000 AND salary <= 90000.

BETWEEN on Dates

-- Employees hired during 2019 or 2020
SELECT name, hire_date
FROM   employees
WHERE  hire_date BETWEEN '2019-01-01' AND '2020-12-31';

Result:

BETWEEN on Strings

BETWEEN also works on text, comparing alphabetically:

-- Names between 'A' and 'D' alphabetically
SELECT name FROM employees
WHERE  name BETWEEN 'A' AND 'D';
-- Returns: Alice, Bob, Carol, David

NOT BETWEEN

-- Salaries outside the 60000-90000 range
SELECT name, salary
FROM   employees
WHERE  salary NOT BETWEEN 60000 AND 90000;

Result: Frank (95000) and Carol (92000) — above the range. David (55000) — below the range.

IN — Matching a List of Values

IN is a cleaner way to write multiple OR conditions. It checks whether a value matches any value in a list.

-- HR or Finance employees
SELECT name, dept
FROM   employees
WHERE  dept IN ('HR', 'Finance');

-- This is equivalent to:
WHERE  dept = 'HR' OR dept = 'Finance'

IN is much more readable when the list is long:

-- Instead of: emp_id=1 OR emp_id=3 OR emp_id=5 OR emp_id=6
SELECT name
FROM   employees
WHERE  emp_id IN (1, 3, 5, 6);

NOT IN — Excluding a List

-- Everyone except IT employees
SELECT name, dept
FROM   employees
WHERE  dept NOT IN ('IT');

-- More practically, exclude multiple values
SELECT name
FROM   employees
WHERE  emp_id NOT IN (2, 4);

IN with a Subquery

IN can check against the results of another query (called a subquery):

-- Employees in departments where the average salary exceeds 70000
SELECT name, dept
FROM   employees
WHERE  dept IN (
    SELECT dept
    FROM   employees
    GROUP BY dept
    HAVING AVG(salary) > 70000
);

-- Safe: the list contains no NULLs
SELECT name FROM employees WHERE dept NOT IN ('IT', 'HR');
-- Returns: Eve (Finance) — works correctly

-- DANGEROUS: if any row in the subquery has a NULL bonus...
SELECT name FROM employees
WHERE  bonus NOT IN (
    SELECT bonus FROM employees   -- this subquery may contain NULLs!
);
-- Returns: ZERO rows, even if many employees clearly have non-matching bonuses

-- SAFE fix: filter NULLs from the subquery
SELECT name FROM employees
WHERE  bonus NOT IN (
    SELECT bonus FROM employees WHERE bonus IS NOT NULL
);

-- BETTER fix: use NOT EXISTS instead
SELECT name FROM employees e1
WHERE  NOT EXISTS (
    SELECT 1 FROM employees e2
    WHERE  e2.bonus = e1.bonus
    AND    e2.emp_id <> e1.emp_id
);

LIKE — Pattern Matching

LIKE lets you search for rows where a text column matches a pattern. It uses two wildcard characters:

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

-- Names starting with 'A' (% after = anything after the A)
SELECT name FROM employees WHERE name LIKE 'A%';
-- Result: Alice

-- Names ending with 'e' (% before = anything before the e)
SELECT name FROM employees WHERE name LIKE '%e';
-- Result: Alice, Eve

-- Names containing 'ar' anywhere
SELECT name FROM employees WHERE name LIKE '%ar%';
-- Result: Carol

-- Names that are exactly 3 characters long
SELECT name FROM employees WHERE name LIKE '___';
-- Result: Bob, Eve (each _ is one character)

-- Second character is 'o'
SELECT name FROM employees WHERE name LIKE '_o%';
-- Result: Bob

Wildcard Pattern Reference

NOT LIKE

-- Names that do NOT start with A or B
SELECT name FROM employees WHERE name NOT LIKE 'A%' AND name NOT LIKE 'B%';

LIKE with ESCAPE — Searching for Literal % or _

What if you need to search for an actual percent sign or underscore in the data (not as a wildcard)?

-- Find products where the name literally contains a % sign
-- Define \ as the escape character
SELECT name FROM products
WHERE  name LIKE '%50\%%' ESCAPE '\';
-- Matches: "50% OFF", "Get 50% discount"

-- Find codes containing a literal underscore
SELECT code FROM products
WHERE  code LIKE 'A\_%' ESCAPE '\';
-- Matches: "A_123", "A_XYZ"

IS NULL / IS NOT NULL — Handling Missing Values

NULL in SQL means "unknown" or "missing". It is not zero, it is not an empty string, it is the absence of any value.

The critical rule: you cannot use = or <> to compare NULL.

Any comparison with NULL using = returns UNKNOWN (not TRUE or FALSE), and rows returning UNKNOWN are excluded just as if they returned FALSE.

-- WRONG — this always returns no rows
SELECT name FROM employees WHERE bonus = NULL;      -- never works!
SELECT name FROM employees WHERE bonus != NULL;     -- never works!

-- CORRECT — use IS NULL / IS NOT NULL
SELECT name FROM employees WHERE bonus IS NULL;
SELECT name FROM employees WHERE bonus IS NOT NULL;

-- Find employees with no bonus (bonus column is NULL)
SELECT name, dept
FROM   employees
WHERE  bonus IS NULL;

Result:

-- Find employees who DO have a bonus
SELECT name, bonus
FROM   employees
WHERE  bonus IS NOT NULL
ORDER BY bonus DESC;

Result:

Why NULL Behaves This Way

Think of it this way: if someone asks "Is the number in this box equal to 5?" and the box is empty (NULL), you cannot say "yes" or "no" — you simply do not know. SQL reflects this reality by returning UNKNOWN, not TRUE or FALSE.

-- SQL three-valued logic demonstration:
NULL = NULL      -- UNKNOWN (not TRUE!)
NULL = 5         -- UNKNOWN
NULL IS NULL     -- TRUE
5 IS NOT NULL    -- TRUE

COALESCE — First Non-NULL Value

COALESCE takes a list of values and returns the first one that is not NULL. It is the standard way to replace NULL with a default value.

-- Replace NULL bonus with 0
SELECT name,
       COALESCE(bonus, 0)           AS bonus_display,
       salary + COALESCE(bonus, 0)  AS total_comp
FROM   employees;

Result:

-- COALESCE with multiple fallback values
-- Use department_email if available, else personal_email, else 'no email'
SELECT name,
       COALESCE(department_email, personal_email, 'no email on file') AS contact_email
FROM   employees;

COALESCE evaluates its arguments left to right and returns the first non-NULL value found. If all arguments are NULL, it returns NULL.

NVL — Oracle's COALESCE Alternative

Oracle has a function called NVL which replaces NULL with a specified value. It only takes two arguments (unlike COALESCE which can take many).

-- Oracle: replace NULL with 0
SELECT name, NVL(bonus, 0) AS bonus
FROM   employees;

-- NVL2: different values depending on whether the first arg is NULL
-- NVL2(expr, value_if_not_null, value_if_null)
SELECT name,
       NVL2(bonus, 'Has bonus', 'No bonus') AS bonus_status
FROM   employees;

NULLIF — Return NULL When Two Values Are Equal

NULLIF(a, b) returns NULL if a equals b, otherwise returns a. It is the inverse of COALESCE in a sense.

Primary use case: preventing division by zero.

-- Without NULLIF: division by zero error if count = 0
SELECT total_sales / total_count AS avg_sale FROM report;

-- With NULLIF: returns NULL instead of error when count = 0
SELECT total_sales / NULLIF(total_count, 0) AS avg_sale FROM report;

-- Another use: treat a specific sentinel value as NULL
-- If dept is 'Unknown', return NULL instead
SELECT name,
       NULLIF(dept, 'Unknown') AS dept_cleaned
FROM   employees;

CASE in WHERE Clause

While CASE is typically used in SELECT, it can also appear in WHERE and ORDER BY:

-- Conditional filter using CASE
SELECT name, dept, salary
FROM   employees
WHERE  CASE
           WHEN dept = 'IT'      THEN salary > 80000
           WHEN dept = 'Finance' THEN salary > 70000
           ELSE                       salary > 55000
       END;

This applies a different salary threshold depending on the department — IT employees must earn more than 80000, Finance more than 70000, all others more than 55000.

Combining Multiple Filter Types — Real-World Examples

Real queries combine many of these operators together:

-- Example 1: Employees hired in 2020, in IT or Finance, with a bonus
SELECT name, dept, salary, hire_date, bonus
FROM   employees
WHERE  hire_date BETWEEN '2020-01-01' AND '2020-12-31'
AND    dept IN ('IT', 'Finance')
AND    bonus IS NOT NULL
ORDER BY salary DESC;

-- Example 2: Not in HR, salary not between 55000 and 65000, name contains 'a'
SELECT name, dept, salary
FROM   employees
WHERE  dept NOT IN ('HR')
AND    salary NOT BETWEEN 55000 AND 65000
AND    LOWER(name) LIKE '%a%'
ORDER BY name;

-- Example 3: High earners with no bonus (potential compensation review)
SELECT name,
       dept,
       salary,
       COALESCE(bonus, 0) AS bonus,
       'Review needed'    AS action
FROM   employees
WHERE  salary > 75000
AND    bonus IS NULL;

Performance Tips — Sargable vs Non-Sargable Predicates

A sargable predicate (Search ARGument ABLE) is one that the database can use an index to accelerate. A non-sargable predicate forces a full table scan.

Non-sargable (slow on large tables) — avoid:

-- Function on the column prevents index use
WHERE UPPER(name) = 'ALICE'
WHERE YEAR(hire_date) = 2020
WHERE salary + 1000 > 80000
WHERE SUBSTR(dept, 1, 2) = 'IT'

-- Leading wildcard prevents index use
WHERE name LIKE '%lice'

Sargable (index-friendly) — prefer:

-- No function on the indexed column
WHERE name = 'Alice'                        -- exact match
WHERE name = 'ALICE' OR name = 'alice'      -- or use case-insensitive collation
WHERE hire_date >= '2020-01-01'             -- range on date
    AND hire_date < '2021-01-01'
WHERE salary > 79000                        -- comparison without arithmetic on col
WHERE name LIKE 'Ali%'                      -- trailing wildcard is sargable

Quick Reference — All Filter Operators

Common Errors

Interview Corner

-- Wrong — returns 0 rows even if commission_pct has NULLs
SELECT * FROM employees WHERE commission_pct != NULL;

-- Correct
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

Related Topics

• Basic SQL — SELECT fundamentals, column aliases, ORDER BY
• Aggregate Functions — GROUP BY and HAVING for filtered aggregations
• Subqueries — subqueries in WHERE for dynamic filtering
• Regular Expressions — REGEXP_LIKE as a powerful alternative to LIKE