FIT2094 Chap.6 SQL Selection, Joins and Aggregation
SQL Selection, Joins and Aggregation
SQL querying begins by defining what one result row represents. FROM and JOIN establish candidate rows, WHERE filters individual rows, GROUP BY forms groups, HAVING filters groups, SELECT shapes the displayed values and ORDER BY arranges the result.
This logical order explains why row conditions and aggregate conditions belong in different clauses even though SELECT appears first in the written syntax.
Joins connect facts through keys and can multiply rows when the relationship grain is misunderstood. Aggregates summarise the resulting rows, while subqueries express nested values or membership tests.
Nulls require IS NULL logic, and COUNT(*) differs from COUNT(column). Date filters should use explicit boundaries rather than formatted strings. A query is complete when boundary data confirms the intended inclusion, exclusion and grain.
Build a boundary dataset before refining syntax.
Include one matching row, one near miss, one unmatched parent, a null in the counted column, two child rows for the same parent and dates exactly at both ends of the requested interval. Predict the result identifiers and aggregate values by hand. If a join duplicates the intended event, either change the join path or count the correct distinct business key; do not add DISTINCT merely to hide unexplained multiplication.
For grouped output, verify that WHERE removes rows before grouping and HAVING removes groups afterward. The test data should make those two stages produce visibly different outcomes.
What this chapter covers
- 01
SELECT expressions, aliases and ordering
- 02
Row conditions, null tests and date boundaries
- 03
ANSI inner joins and relationship grain
- 04
Outer joins and preservation of unmatched rows
- 05
SUM, AVG and COUNT semantics
- 06
GROUP BY and aggregate conditions in HAVING
- 07
Subqueries and result-set verification
Worked example · free
Count completed bookings by building
- +1Set the result grain to one row per building and join BOOKING to ROOM on the room identifier.
- +2Filter status to Completed and use a half-open September interval in WHERE so every time in the month is included without guessing a final second.
- +2Group by the building attribute and calculate COUNT(*) because each joined row represents one booking under the stated schema.
- +2Place the threshold COUNT(*) at least three in HAVING because it describes a group, then order by the count descending.
- +1Test buildings with two and three completed bookings, a cancelled booking and a booking exactly at the next month's start.
Key terms
- Query grain
- The entity or event represented by one result row, used to reason about joins, duplicates and aggregation.
- Inner join
- A join returning only row combinations that satisfy the stated relationship condition.
- Outer join
- A join preserving unmatched rows from one or both sides and representing the absent match with null values.
- Aggregate function
- A function such as SUM, AVG or COUNT that derives one summary from a set of input rows or values.
- Group condition
- A predicate applied after grouping, usually written in HAVING and often involving an aggregate.
- Subquery
- A query nested inside another statement to provide a value, set, existence test or row source.
SQL Selection, Joins and Aggregation FAQ
What is the difference between WHERE and HAVING?
WHERE filters individual rows before groups are formed. HAVING filters grouped results after aggregation. Put status and date conditions in WHERE, and put thresholds such as COUNT greater than a value in HAVING.
Why does a join sometimes duplicate my rows?
The joined relationship may be one-to-many or many-to-many, so one input row legitimately matches several others. State the intended result grain, inspect the keys and decide whether aggregation or a different join is needed before using DISTINCT.
How does COUNT handle null values?
COUNT(*) counts rows, while COUNT(column) counts only rows where that column is non-null. The distinction matters after outer joins and whenever missing values are meaningful. Choose the expression that matches the event being counted.
What is a reliable way to filter a month?
For date or timestamp values, use a half-open interval from the month's start inclusive to the next month's start exclusive. This includes every stored time in the month and avoids assumptions about the final second.
Assessment move
Practise SQL from questions, not isolated clause drills. Begin by writing the requested grain and the tables that own each required attribute. Draw the join path on keys, then predict how each join changes the row count. Add row-level conditions in WHERE, form groups only when the output requires summaries, and place aggregate thresholds in HAVING.
Test nulls, an unmatched parent, duplicate child rows, exact date boundaries and values at the threshold. Explain why one row appears and another does not. When results are wrong, inspect the earliest stage where the row set diverges instead of adding DISTINCT or extra filters at random.
Save the predicted result table beside the query and reconcile every discrepancy to a join, filter, group or null rule before changing syntax.
