LEFT, RIGHT & FULL OUTER JOIN

An INNER JOIN keeps only matched rows, but many real questions are precisely about the unmatched ones: which players have not batted yet, which teams have no recorded matches, which records failed to link. Outer joins exist to preserve rows that lack a counterpart, filling the missing side with NULLs instead of discarding them, so absence itself becomes visible and queryable.

There are three outer join variants. A LEFT JOIN keeps every row from the left table, a RIGHT JOIN keeps every row from the right, and a FULL OUTER JOIN keeps every row from both. Wherever a match is missing, the columns from the absent side come back as NULL. Choosing the right variant is simply deciding which table's rows you refuse to lose.

Outer joins are essential for completeness and for data-quality work. Reports that must list every player regardless of activity, dashboards that show zero where there is no data, and checks that hunt for orphaned or missing records all depend on outer joins. Mastering how they generate NULLs, and how filtering interacts with those NULLs, prevents a whole family of subtle and common reporting errors.

Analogy🏏Cricket

🏏 Think of it like cricket: Imagine the end of an IPL innings when the scorer announces the team's final total. Just as the scorer does not read out all one hundred and twenty individual deliveries but instead sums them into a single team score, SUM folds many row values into one number. Just as the commentator reports the highest individual score, the average partnership, and the number of wickets, MAX, AVG, and COUNT each summarise the innings differently. Just as a not-out batsman with no deliveries faced does not lower the team average, aggregates skip NULLs when computing. Just as the announcement is one figure no matter whether the innings had fifty deliveries or two hundred and fifty, an aggregate collapses any number of rows into a single result. This reveals why summaries answer the questions about the whole that individual deliveries cannot.