LEARN. PRACTICE. GROW.Concept and mechanism
A join combines rows according to a condition. INNER JOIN requires a match; LEFT JOIN preserves the left side and supplies NULL for unmatched-side columns. An ON filter selects matches, whereas a later WHERE can remove preserved rows. One-to-many relationships change cardinality: a position with two contacts and three tags can appear six times when both details are combined. Before summing, define each set’s grain and aggregate or separate relationships where necessary. SUM DISTINCT is not a universal repair because different positions can have equal amounts. Self-joins and nonequijoins need the same care with keys, aliases, and multiplicity. Check expected cardinality before optimizing execution.
Guided application
In a fictional report, count rows and business keys before and after each join. For funds without movements, COUNT(*) counts the preserved row, but COUNT(m.id), with id a non-null key, counts zero matches. Group functions normally ignore NULL in their expression: AVG of 10, NULL, and 20 is 15; replacing NULL with zero changes the result to 10. Use WHERE to select movements before grouping and HAVING to filter calculated groups. Document the difference between no movement, unknown amount, and a zero sum. These states can require different operational responses even when presentation tries to display them identically.
SELECT f.id, COUNT(m.id) FROM funds f LEFT JOIN movements m ON m.fund_id=f.id AND m.status='FAILED' GROUP BY f.id
Common pitfalls
WHERE filtering away preservation; two detail relationships as one; COUNT(*) as child count.
Related topics: Model, SELECT, and result population · Functions, conversions, and data contracts · Subqueries, sets, and top-N
Establish cardinality before trusting a sum or percentage.
Reference: Join preservation and multiplicity · 1Z0-071 public objectives inspected 2026-09-30; revision date not published