SELECT … FROM … WHERE: Projection, Filtering, DISTINCT & Predicates

◈ 6 cards

Project columns, filter rows with comparison and Boolean operators (minding NOT→AND→OR precedence), deduplicate with DISTINCT, and use LIKE, BETWEEN, IN, and IS NULL — the foundation under every query on the exam.

The core of every query

The SELECT … FROM … WHERE skeleton does two things: projection (which columns appear, chosen in the SELECT list) and selection / filtering (which rows appear, decided by WHERE). SELECT * projects every column; naming columns projects just those, in the order you list them.

SELECT name, salary FROM employees WHERE state = 'AB';

DISTINCT removes duplicate rows from the result — and it applies to the whole projected row, not just the first column:

SELECT DISTINCT state FROM employees;        -- 3 unique states
SELECT DISTINCT state, dept_id FROM employees; -- unique (state, dept_id) PAIRS

Comparison and the predicate toolkit

The comparison operators are = <> < > <= >= (<> is “not equal”). Beyond raw comparison, four predicates do most of the work:

LIKE — pattern match with wildcards: % = any run of characters, _ = exactly one character. name LIKE 'R%' matches every name starting with R; code LIKE '_-drawer' matches 3-drawer.
BETWEEN x AND y — inclusive range, identical to >= x AND <= y.
IN (list) — membership: state IN ('AB','BC').
IS NULL / IS NOT NULL — the only correct null test. = NULL is always unknown, never true, so it silently matches nothing.

Worked example: Boolean precedence (a guaranteed trap)

SQL evaluates Boolean operators in a fixed order: NOT first, then AND, then OR last. That order bites without parentheses. Consider:

SELECT name, title, salary FROM employees
WHERE title = 'Salesperson' OR title = 'Manager' AND salary > 115000;

Because AND binds tighter than OR, SQL reads this as:

(title = 'Salesperson')  OR  (title = 'Manager' AND salary > 115000)

so you get all salespeople (regardless of salary) plus only the managers earning over 115 000. If you meant “salespeople or managers, but only those earning over 115 000,” you must force the grouping with parentheses:

WHERE (title = 'Salesperson' OR title = 'Manager') AND salary > 115000

The safe habit is to always parenthesize a mix of AND and OR, even when precedence happens to be on your side — it documents intent and survives later edits.