INFO20003 Chap.5 SQL Query Construction
SQL Query Construction
SQL becomes predictable when its logical evaluation order is separated from its written order. This chapter builds row sources with FROM and JOIN, filters rows with WHERE, creates groups, filters groups with HAVING and only then forms the selected result. It emphasises outer join preservation, NULL's three-valued logic, result grain, self joins, EXISTS patterns and aggregation that avoids fan-out.
Begin every query by declaring its output grain, such as one row per customer or one row per customer-month. FROM and JOIN construct the provisional row set at a grain determined by their matches. WHERE removes individual rows before grouping.
GROUP BY forms one group for each distinct grouping combination, aggregate functions reduce each group, HAVING removes groups, and SELECT constructs the visible columns; DISTINCT then removes duplicate result rows and ORDER BY establishes presentation order. This logical sequence explains why a SELECT alias is not generally available to WHERE and why aggregate conditions belong in HAVING.
Outer joins require deliberate predicate placement. An unmatched left row receives null markers for right-side attributes. A right-side test in WHERE evaluates as unknown for that row and removes it, whereas the same test in ON limits which right rows match while retaining the left row. COUNT(*) counts the preserved placeholder; COUNT(right_key) ignores its null and yields zero matches.
NULL is tested with IS NULL rather than equality, and unknown affects NOT IN when its subquery can return null. EXISTS and NOT EXISTS express presence and absence while preserving one outer row regardless of how many matches exist. Ordinary joins are appropriate when the result needs one row per pair or right-side attributes.
Aggregation must control fan-out: joining two independent one-to-many details before summing can multiply both measures, so pre-aggregate each detail to the target grain or use separate correlated summaries. Self joins need aliases that state roles, and recursive relationships need a termination strategy beyond one fixed join. For set operations, match compatible columns and decide whether duplicate elimination is required.
Validate the finished query by tracing an ordinary match, an unmatched entity, a duplicate match and a null through every clause. The expected number and meaning of rows at each stage is a stronger debugging tool than syntax inspection alone.
What this chapter covers
- 01
DDL and DML
- 02
Logical query order
- 03
Inner and outer joins
- 04
NULL predicates
- 05
Self joins
- 06
EXISTS and NOT EXISTS
- 07
Grouping and HAVING
- 08
Fan-out control
Worked example · free
Count plays including zero
- 1Begin from Artist to preserve the complete artist population.
- 1Left join Track and Play through their foreign keys.
- 1Keep the date predicate in the Play join condition.
- 1Group by artist identity and name.
- 1Count the nullable matched play key so unmatched artists produce zero.
Key terms
- Logical query order
- The conceptual sequence in which SQL forms sources, filters, groups, selects and sorts.
- Outer join
- A join that preserves unmatched rows from a declared side and supplies nulls for missing attributes.
- NULL
- A marker for missing or inapplicable information that produces unknown in ordinary comparisons.
- Self join
- A query that assigns distinct aliases to multiple logical roles of one relation.
- Result grain
- The entity, event or group represented by one output row.
- Fan-out
- Row multiplication caused by joining one row to several matching rows before aggregation.
SQL Query Construction FAQ
Why should I declare the output grain first?
The grain determines whether a join is allowed to multiply rows, which columns belong in GROUP BY and what one aggregate value represents. Writing one row per customer, order or match before the query makes accidental fan-out visible and helps choose between an ordinary join, EXISTS or pre-aggregation.
Why does a WHERE condition sometimes undo a left join?
An unmatched right-side row contains NULL. A right-side comparison in WHERE becomes unknown and the row is removed, so the preserved population disappears. Put the qualifying optional-side predicate in the join condition when zero matches must remain.
When should I use EXISTS instead of a join?
Use EXISTS when the question needs to test whether at least one related row exists but does not need attributes from that row. It expresses the intent directly and avoids multiplying the outer row by the number of matches.
How do I prevent wrong aggregate totals?
Declare the intended grain, inspect every one-to-many join and pre-aggregate independent many-side data before combining it. DISTINCT cannot repair a sum already multiplied by fan-out, so the source grain must be corrected.
Exam move
Maintain a library of query shapes, each with a boundary case. Include preserving zero-count entities, finding rows with and without related records, self joins for hierarchies, aggregates by entity identity and universal conditions. For every practice query, write the target grain at the top and trace one ordinary row, one unmatched row and one NULL value through the clauses.
Explain why each predicate belongs in ON, WHERE or HAVING. Write table definitions with explicit keys and references so the SQL remains connected to the model rather than becoming isolated syntax memorisation.
Working through SQL Query Construction in INFO20003? Sia is AskSia’s AI Computer Science tutor — ask any INFO20003 SQL Query Construction question and get a clear, step-by-step explanation grounded in how INFO20003 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.