Premier League Predictor: FastAPI & PostgreSQL — Chapter 7, Exercise 1
====================================================

TASK
Explain why team_fixtures is built with a UNION ALL rather than a
single SELECT with an OR condition on home_team_id/away_team_id, and
what would go wrong with a team's own goals_for/goals_against totals if
the UNION ALL's two branches didn't flip which score column is "for"
and which is "against."

SOLUTION

A single SELECT with WHERE home_team_id = X OR away_team_id = X would
return every fixture a team played, but each returned row would still
only have one fixed pair of columns — home_score and away_score — with
no way to know, from the row alone, which of those two columns actually
belongs to the team being queried for. A team that was away in one
fixture and home in another needs its OWN score read from a different
column depending on which role it played that match, and a plain OR
condition doesn't reshape the row to reflect that at all.

The UNION ALL solves this by running two separate SELECTs and stacking
their results: one branch reads home_team_id as team_id and home_score
as goals_for (with away_score as goals_against), the other reads
away_team_id as team_id and away_score as goals_for (with home_score as
goals_against). After the UNION ALL, every row — regardless of which
original branch it came from — has the same shape: team_id,
goals_for, goals_against, always meaning exactly what those column
names say for that specific team. That's what makes a single, ordinary
GROUP BY team_id afterward correct.

If the two branches didn't flip goals_for/goals_against between them —
for example, if both branches used home_score as goals_for regardless
of which team's row it was — an away team's own goals_for would
actually be reading the OPPONENT's home goals, and its goals_against
would be reading its own real goals scored. Every away performance
would have its actual result backwards: a team that won 3-0 away would
show up as having conceded 3 and scored 0 for that match, corrupting
won/drawn/lost, goals_for, goals_against, and points for every team
with any away fixtures at all.

WHY THIS WORKS AS AN ANSWER
----------------------------
It explains precisely why a single OR-based SELECT can't reshape a row
per-team the way this query needs, describes what the UNION ALL's two
branches actually do to produce a consistent shape, and traces the
concrete, specific corruption (goals swapped for every away
performance) that skipping the flip would cause.