Topic 7.5
Joins
In one line
JOIN, LEFT JOIN, GROUP BY, and the cost story: joins are the reason you vertically grow a SQL DB.
Think of it like this
Combining two spreadsheets by matching a shared column, like matching 'student ID' in a marks sheet with 'student ID' in an attendance sheet, to build one combined view.
Key ideas
- 01
INNER JOIN: rows present on both sides. LEFT JOIN: keep all left rows, nulls where right is missing.
- 02
GROUP BY / HAVING: aggregation with a predicate on the aggregate.
- 03
Nested-loop / hash join / merge join: the planner's three strategies; hash join dominates for big equi-joins.
- 04
The scaling angle: joins across tables = work on ONE node — this is exactly why NoSQL (denormalized) exists.
- 05
Indexing join keys is how joins stay seconds instead of minutes.
- 06
Explain the shape: EXPLAIN ANALYZE shows the join strategy + row estimates — always run it in interviews that allow it.
Java / Spring map
- →
JPA @ManyToOne fetch; query via @Query or QueryDSL; watch the N+1 trap with lazy collections.
Explain without notes
What does a LEFT JOIN return when the right side has no match — and why is that sometimes exactly the bug?
Practice
Write the JOIN queries for Splitwise 'user's balances in a group' and 'group summary'.
Trade-offs
- ↔
Joins make queries easy and vertical scaling mandatory; materialized views trade freshness for speed.
Completion checklist
I can write the four core join shapes from a word problem.