The SELECT Statement and Its Clause Order
SELECT is the retrieval half of SQL, and the reason SQL is called a declarative language: you specify what the result should look like and the DBMS decides the execution plan. The result of any SQL query is itself a table, which is what makes queries composable — it can be joined (sql-joins), nested inside another query (sql-nested-queries), stacked with another result set (sql-set-operations), or stored as a view (sql-views). Introduced in 2026-03-30-basic-sql-ddl-and-dml and extended across 2026-04-06-aggregation-grouping-and-multiple-relation-queries and 2026-04-13-nested-queries-views-and-generative-ai.
The full clause skeleton
SELECT [DISTINCT] <target list>
FROM <table list>
[WHERE search condition]
[GROUP BY <grouping attributes>]
[HAVING <group conditions>]
[ORDER BY column [ASC|DESC] {, column [ASC|DESC]}];Only SELECT and FROM are compulsory; the clauses must appear in this order when present.
Logical evaluation order
The order the clauses are written is not the order they take effect. Reading a query as a pipeline — each stage handing a table to the next — explains most of SQL’s otherwise arbitrary-looking rules:
| # | Clause | Operates on | Produces |
|---|---|---|---|
| 1 | FROM |
The named tables (joined, if more than one) | One combined table |
| 2 | WHERE |
Individual rows | The rows that satisfy the condition |
| 3 | GROUP BY |
The surviving rows | One group per distinct combination of grouping attributes |
| 4 | HAVING |
Whole groups | The groups that satisfy the condition |
| 5 | SELECT |
Each group (or each row, if no grouping) | The output columns |
| 6 | ORDER BY |
The finished result table | The same rows, sorted |
Three of the course’s recurring rules fall straight out of this:
- An aggregate cannot appear in
WHERE. At stage 2 no groups exist yet, so there is nothing to aggregate over —WHERE salary > AVG(salary)fails with invalid use of group function, and the fix is to compute the aggregate in a subquery instead (see sql-nested-queries). HAVINGmay use aggregates,WHEREmay not, becauseHAVINGruns at stage 4, after the groups exist.WHEREfilters rows,HAVINGfilters groups. They are not interchangeable, and a query can sensibly use both — see sql-aggregation-and-grouping.
The placement of SELECT before ORDER BY is not a guess: a sort key may be given as a column position in the target list (ORDER BY 2 DESC), which is only meaningful if the target list has already been built. Correspondingly, the two clauses that must be able to see raw rows — WHERE and GROUP BY — can only name attributes of the tables in FROM, never an alias invented by SELECT.
Projection and selection
The course names the two halves of a simple query after the relational operations they perform:
| Clause | Operation | Cuts the table |
|---|---|---|
SELECT |
Projection — choose the attributes | Vertically |
WHERE |
Selection — choose the tuples | Horizontally |
Note the terminological trap: the relational operation called selection is the WHERE clause, not the SELECT clause.
Projection, duplicates and bag semantics
SELECT [DISTINCT] (<attribute list> | *)
FROM <table list>
[WHERE <condition>];SQL relations are bags (multisets), not sets: projection does not eliminate duplicates by default, unlike the pure relational-model. DISTINCT restores set semantics; * is the wildcard for “all columns”.
-- All the years a movie was produced, with and without duplicates
SELECT year FROM Movie;
SELECT DISTINCT year FROM Movie;The consequence to watch for: projecting away the columns that made rows distinct leaves duplicate rows behind. Projecting (score1, score2) from a table of match results keeps two separate (5, 3) rows if two different fixtures both ended 5–3; SELECT DISTINCT collapses them into one and the row count drops accordingly.
Expressions and aliases
The target list can contain arithmetic (+ - * /) over numeric attributes, string/date functions, and aggregates — each optionally renamed with AS:
-- Names, salaries, and salaries with a 17% loading, for dept 6
SELECT name, salary, 1.17 * salary AS 'includingSuper'
FROM Employee
WHERE dNum = 6;
-- Build an employee code FirstName-LastName-yyyy
SELECT CONCAT(FirstName, '-', LastName, '-', YEAR(DOB))
FROM Employee;Aliases also disambiguate attributes across tables and enable self-joins — see sql-joins.
The WHERE clause
The <search condition> is a conditional (Boolean) expression identifying the tuples to retrieve. It may contain:
| Form | Use |
|---|---|
=, <>, <, <=, >, >= |
Compare an attribute to a value or another attribute |
AND, OR, NOT |
Combine conditions — bracket them, the grouping is the whole meaning |
LIKE |
Substring matching: % is zero or more arbitrary characters, _ is exactly one |
IN |
Membership in a list: lastName IN ('Jones', 'Wong', 'Harrison') |
IS |
Null-checking — dNum IS NULL; = does not work here, see sql-nulls |
+ - * /, date/time functions |
Arithmetic on the row’s own values: salary * 2 > 50000, YEAR(GETDATE() - dob) > 55 |
BETWEEN … AND … |
Inclusive range: salary BETWEEN 10000 AND 30000 |
-- Names of employees in dept 4 earning > 25000, or dept 5 earning > 30000
SELECT name
FROM Employee
WHERE (dNum = 4 AND salary > 25000) OR (dNum = 5 AND salary > 30000);
-- Permissions to edit commercial (.com) websites
SELECT WebsiteURI
FROM Permission
WHERE GrantType = 'Edit' AND WebsiteURI LIKE '%.com';A disjunction of conjunctions is exactly as literal as it looks: a row qualifies only if it satisfies one whole bracket. Rows with impressive values in the right columns but the wrong combination — 8 runs scored but 12 conceded, against (runsFor >= 6 AND runsAgainst <= 4) — do not qualify.
Dates are compared either as a quoted 'YYYY-MM-DD' literal or as the bare number in that order; both forms appear in the course and both work, because the ordering is the same either way:
SELECT name, dob FROM Employee WHERE dob < '1965-01-01';
SELECT name, dob FROM Employee WHERE dob < 19650101;Grouping the connectives
The course brackets its compound conditions rather than leaning on precedence, and that is the habit to copy: (A AND B) OR (C AND D) written out is unambiguous to you and to the marker. Where precedence still decides the meaning, it is NOT first, then AND, then OR — so NOT binds to the single comparison next to it, not to the rest of the condition. sql-dml works that case through on a DELETE, where misreading it costs the whole table rather than a few rows.
Written without brackets, A AND B OR C AND D groups as (A AND B) OR (C AND D) — which is usually what was meant, but relying on that is how a condition ends up meaning something you did not intend. Bracket it.
ORDER BY
[ORDER BY column [ASC|DESC] {, column [ASC|DESC]}]Sorting is the last stage, applied to the finished result. A sort key is either a column name or a column position in the target list; multiple keys are applied left to right, each with its own ASC (default) or DESC.
-- By department ascending, then salary descending within each department
SELECT dNum, name, salary
FROM Employee
ORDER BY dNum ASC, salary DESC;
-- Sorting a grouped result by its count, greatest first: the count is
-- the 2nd column of the target list
SELECT VehiclePurpose, COUNT(*)
FROM Vehicle
GROUP BY VehiclePurpose
ORDER BY 2 DESC;The positional form is what makes a computed column sortable at all — the aggregate itself belongs to SELECT and HAVING (sql-aggregation-and-grouping), and ORDER BY reaches it by pointing at the column it produced.
Later keys only break ties left by earlier ones, so ORDER BY c DESC, b ASC puts every row with a larger c ahead of (5, 5, 5) regardless of its b. Where two rows tie on all the sort keys, their relative order is unspecified — never rely on it.