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

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.