Filtering Data

The WHERE clause filters rows before grouping.

Concept

The WHERE clause filters rows before grouping. Only rows for which the condition evaluates to TRUE are kept. Rows evaluating to FALSE or UNKNOWN are discarded.

Why It Matters

Filtering is how you answer specific questions ("orders from India this month"). Correct filtering is also the biggest single factor in query performance, because filters on indexed columns let the database avoid reading the whole table.

Syntax

Not equal (standard) status <> 'CANCELLED' Not equal (widely supported alias) status != 'CANCELLED' salary > 100000 price < 1000 Greater than or equal Less than or equal order_date <= DATE '2026-01-31' Logical operators and precedence Negates a condition (evaluated first) Both must be TRUE (evaluated second) At least one TRUE (evaluated last) BETWEEN a AND b Inclusive range: >= a AND <= b Equal to any value in list NOT IN (list)

Not equal to any value (dangerous with NULLs) LIKE pattern Pattern match with % and _ NOT LIKE pattern Negated pattern match Value is not NULL

SELECT columns
FROM table
WHERE condition1 [AND | OR condition2] ...;

Example

8.1 Basic comparisons String comparisons may be case-sensitive or not depending on the DBMS and collation: MySQL (default collations) and SQL Server (default CI collations) are case-insensitive; PostgreSQL and Oracle are case-sensitive; SQLite's = is case-sensitive by default.

8.2 AND, OR, NOT, and precedence Because AND binds tighter than OR, this means dept_id = 1 OR (dept_id = 2 AND salary > 100000):

With parentheses:

Always use parentheses when mixing AND and OR. BETWEEN 150000 AND 90000 (reversed bounds) returns nothing.

For timestamps, BETWEEN is risky: order_ts BETWEEN '2026-01-01' AND '2026-01-31' misses everything after midnight on Jan 31. Use a half-open range:

8.4 IN and NOT IN Kiran (dept_id NULL) is not returned. NULL NOT IN (1, 2) means NULL <> 1 AND NULL <> 2 → UNKNOWN AND UNKNOWN → UNKNOWN → row discarded.

The famous NOT IN trap: if the list contains a NULL, NOT IN returns no rows at all:

This matters most with subqueries (NOT IN (SELECT col ...) where col can be NULL). Prefer NOT EXISTS (Module 14).

8.5 LIKE and pattern matching Zero or more characters Mumbai, Milan, M Exactly one character Last query result:

Escaping wildcards (search for a literal % or _):

Case-insensitive matching:

Beyond LIKE: regular expressions via ~ (PostgreSQL), REGEXP / REGEXP_LIKE (MySQL 8), REGEXP_LIKE (Oracle). SQL Server supports LIKE with character classes [a-c] and added REGEXP_LIKE in SQL Server 2025.

Performance note: LIKE 'abc%' (prefix) can use a B-tree index; LIKE '%abc' (leading wildcard) usually cannot.

8.6 IS NULL and IS NOT NULL 8.7 NULL comparison — three-valued logic

SQL uses three truth values: TRUE, FALSE, UNKNOWN. Any comparison involving NULL (=, <>, <, >, LIKE, IN) returns UNKNOWN.

WHERE keeps only TRUE. Therefore:

Null-safe equality:

SELECT emp_id, first_name, salary
FROM employees
WHERE salary > 100000;

SELECT order_id, status FROM orders WHERE status <> 'DELIVERED';

SELECT emp_id, first_name, dept_id, salary
FROM employees
WHERE dept_id = 1 OR dept_id = 2 AND salary > 100000;

SELECT emp_id, first_name, dept_id, salary
FROM employees
WHERE (dept_id = 1 OR dept_id = 2) AND salary > 100000;

SELECT product_name FROM products WHERE NOT category = 'Electronics';
-- Desk Chair, Notebook, Standing Desk

SELECT emp_id, first_name, salary
FROM employees
WHERE salary BETWEEN 90000 AND 150000;    -- inclusive on both ends

WHERE order_ts >= TIMESTAMP '2026-01-01 00:00:00'
  AND order_ts <  TIMESTAMP '2026-02-01 00:00:00'

SELECT emp_id, first_name, dept_id FROM employees WHERE dept_id IN (2, 3);

SELECT emp_id, first_name, dept_id FROM employees WHERE dept_id NOT IN (1, 2);

SELECT first_name FROM employees WHERE dept_id NOT IN (1, NULL);
-- returns 0 rows: for Rahul, 2 <> 1 is TRUE but 2 <> NULL is UNKNOWN → TRUE AND UNKNOWN = UNKNOWN

Expected Result

Find Indian customers who have an email address, or any customer from the UK:

SELECT customer_id, name, country
FROM customers
WHERE (country = 'India' AND email IS NOT NULL)
   OR country = 'UK';

Common Mistakes

WHERE col = NULL instead of IS NULL.

Mixing AND/OR without parentheses.

NOT IN with a list or subquery containing NULL.

BETWEEN on timestamps missing the last day.

Using LIKE without wildcards (LIKE 'Mumbai' behaves like = but may be slower and confusing).

Assuming case-insensitivity (or sensitivity) without knowing the collation.

Filtering with functions on indexed columns (WHERE YEAR(order_date) = 2026) — prevents index use; prefer ranges.

Comparing values of different types (WHERE phone = 9876543210 when phone is VARCHAR) causes implicit conversion and can disable indexes.

Best Practices

Use parentheses generously.

Use half-open date ranges (>= start AND < next_start).

Prefer NOT EXISTS over NOT IN for subqueries.

Keep filtered columns "bare" (sargable), e.g., order_date >= DATE '2026-01-01' rather than EXTRACT(YEAR FROM order_date) = 2026.

Match literal types to column types.

Interview Relevance

Very high: "Why does = NULL not work?", "What does NOT IN do with NULLs?", "BETWEEN inclusive or exclusive?", "Difference between % and _?", "What is three-valued logic?".

Practice Questions

List products priced between 1000 and 20000 (inclusive).

List employees whose last name ends with 'a'.

List orders placed in February 2026 using a half-open range.

List customers not from India or Italy.

Count employees who are not in department 1, including those with no department.

Predict: SELECT COUNT(*) FROM customers WHERE email NOT LIKE '%example.com';