On this page
The core idea
A join combines rows from two tables where a condition matches. An inner join keeps only rows that match on both sides. A left join keeps every row from the left table and fills the right side with NULL when there is no match. When one row on the left matches several rows on the right, the left row appears several times. Most join mistakes are not syntax errors; they are mismatches between the rows the query produces and the rows the question needed.
A practical approach
Step 1
Write a sentence that describes one output row, such as 'one row per course with its number of enrolled learners'. This sentence defines the grain of the result.
Step 2
Identify the table that already has one row per output row; that table usually goes first.
Step 3
Choose the join type by asking whether rows without a match should appear. Courses with no enrolments need a left join; reports about actual enrolments can use an inner join.
Step 4
When joining to a table with many rows per key, aggregate it first in a subquery or common table expression, then join the aggregated result.
Step 5
Check the result with counts: compare COUNT(*) with the number of rows you expected and look for duplicated keys with GROUP BY ... HAVING COUNT(*) > 1.
Worked example
A report should list every course with its number of enrolments and its average rating. Joining courses to enrolments and to ratings in one query multiplies rows: a course with 10 enrolments and 4 ratings appears 40 times, so both the count and the average are wrong. Aggregating enrolments per course and ratings per course separately, then left joining both results to courses, produces exactly one correct row per course, including new courses with no data.
A mistake to avoid
Do not hide duplicate rows by adding DISTINCT without understanding why they appeared. DISTINCT can mask a join that is multiplying rows, and aggregates computed before the DISTINCT remain wrong.
Try it yourself
Take a query in your project that joins three or more tables. Write the one-sentence description of its output row, run a count check and look for duplicated keys. Rewrite it with pre-aggregation if any key repeats.
Your implementation checklist
- I can state the grain of every query result
- The driving table has one row per output row
- Join types are chosen by whether unmatched rows should appear
- One-to-many tables are aggregated before joining
- Counts and duplicate-key checks confirm the result
Share or cite this guide
Innovate with Ajay Editorial (2026-10-07). Write SQL Joins by First Naming the Rows You Expect. https://innovatewithajay.com/blogs/write-sql-joins-by-naming-the-rows