Premier League Predictor: Django & MySQL — Chapter 7, Exercise 2
====================================================

TASK
Explain why normalizing home and away fixture rows into one shared
shape with UNION ALL before grouping avoids the join-multiplication
problem entirely, rather than just producing a smaller version of the
same bug.

SOLUTION

The naive query's bug came from combining TWO separate relations
(home_fixtures and away_fixtures) in a single JOIN, which forces the
database to produce every pairing between a row from one relation and
a row from the other — a genuine cross-join, since nothing in the data
says which home fixture should be matched with which away fixture.

The UNION ALL subquery avoids this by never joining those two sets of
rows against each other at all. Instead, it runs two separate,
independent SELECT statements — one pulling every played home fixture
(as team_id, goals_for, goals_against), the other pulling every played
away fixture in the same shape (team_id, goals_for, goals_against) —
and stacks their results vertically into one combined result set. A
team with 4 home fixtures and 4 away fixtures ends up with exactly
4 + 4 = 8 rows total in this combined set, not 4 x 4 = 16. Each real
fixture contributes exactly one row, no matter which side of the
ball it was on.

The GROUP BY team_id that follows then sums over this already-flat,
8-row set directly: SUM(goals_for) simply adds up 8 real numbers, one
per fixture, giving the correct 14. There's no pairing step anywhere
in this process for two unrelated fixtures to accidentally get
combined with each other, because the union happens BEFORE any
aggregation runs, not after — the two relations are reduced to one
uniform relation first, and only then does GROUP BY/SUM ever see the
data. That's the structural reason this sidesteps the bug entirely
rather than just making the multiplication factor smaller: there's no
cross-join left in the query for any row to be duplicated by.

WHY THIS WORKS AS AN ANSWER
----------------------------
It explains the specific structural difference (stacking rows via
UNION ALL vs. joining two relations against each other), shows that
each real fixture still contributes exactly one row to the combined
set regardless of home/away fixture counts, and makes explicit that
the union happens before aggregation, which is why no cross-join —
and therefore no multiplication of any size — exists anywhere in the
corrected query.