SELECT, WHERE and NULL: Filtering and Query Order

Before you can write good queries, you need to know the order in which SQL evaluates them and how NULL changes the rules. These two ideas explain most "why does my query return the wrong rows?" questions.

The logical order of a SELECT statement

You write SELECT first, but the database processes the clauses in a different order.

This is the logical order. The optimizer may execute the query differently, but the result must be the same as if it followed these steps. Three consequences interviewers love to ask about:

• An alias defined in SELECT can be used in ORDER BY (step 7) but not in WHERE (step 2).
• You cannot use an aggregate such as COUNT(*) in WHERE, because grouping has not happened yet. Use HAVING.
• You cannot use a window function in WHERE either. Wrap it in a CTE or subquery.

Filtering with WHERE

SELECT name, salary
FROM employees
WHERE department_id IN (1, 2)
  AND salary BETWEEN 55000 AND 80000
  AND name LIKE '%a';

• IN (1, 2) is shorthand for department_id = 1 OR department_id = 2.
• BETWEEN a AND b is inclusive at both ends.
• LIKE: % matches any number of characters, _ matches exactly one. SQL Server also supports character classes such as [A-C]%.
• AND binds tighter than OR. This surprises beginners:

-- Means: (department 1 AND salary above 70000) OR department 2
WHERE department_id = 1 AND salary > 70000 OR department_id = 2

-- Usually what you meant
WHERE department_id IN (1, 2) AND salary > 70000

Use parentheses whenever you mix the two.

NULL: the value that means "unknown"

NULL is not zero and not an empty string. It means the value is missing or unknown, and SQL uses three-valued logic: a condition can be TRUE, FALSE or UNKNOWN. WHERE keeps only rows where the condition is TRUE.

So this query returns nothing, even though Farid has no salary:

A subtler trap: also drops rows where is , because is . To keep them, write .

The same logic makes dangerous when the list contains a . See subqueries, EXISTS and CTEs.

NULL in aggregates and expressions

• counts rows. counts only rows where the column is not .
• , , and ignore . of 10, 20 and is 15.
• Any arithmetic with gives : is . In SQL Server, is also by default, so use , which treats as an empty string.

ISNULL, COALESCE and NULLIF

The "first argument's type" rule can silently truncate data:

replaces a with a value. does the opposite: it turns a value into .

Sorting: tables have no inherent order

A table is an unordered set of rows. Without , the order of the result is not guaranteed, even if it looks stable today. Always sort when the order matters, and add a tie-breaker so that equal values come out in a predictable order:

Where do s go? SQL Server treats as the lowest value, so it sorts first in ascending order. PostgreSQL treats it as the highest, so it sorts last. Check your database, or handle it explicitly.

Limiting and paging rows

keeps the first rows (add to also keep rows tied with the last one). is the ANSI-standard way to page. In SQL Server it requires . MySQL and PostgreSQL also offer .

Dates: the BETWEEN trap

If contains a time, this query misses most of the last day:

The reliable pattern is a half-open range: start on or after the first day, and strictly before the day after the last one. See filter by date range. For columns in SQL Server, write date literals as , which is read the same way under every language setting.

A habit that saves careers

and without a clause change every row. Run the matching first, do the change inside a transaction, and check the row count. See safe update with a transaction.

Practise

1. On the practice tables from the roadmap, predict, then run: . Which employees are missing, and why?
2. Rewrite with parentheses to say what you mean.
3. Page through employees three at a time using .