Join people and tasks without losing rows

What you will learn

• Compare INNER JOIN and LEFT JOIN results.
• Count child tasks without counting an unmatched placeholder row.
• Keep people with zero matching tasks when filtering a report.

Theory: one person can produce several result rows

A join combines rows using a condition. This lab matches a task's person_id to a person's id. INNER JOIN includes matching pairs. LEFT JOIN also keeps a person without a matching task; the task columns in that result row are NULL. Asha has two tasks, so her name appears twice in a detailed report. More joined rows do not imply more people.

WHERE filters the joined result. A condition on a nullable child column can remove the unmatched rows you wanted to keep. Put a child-matching condition in ON when the goal is to retain every person. Choose this from the report's meaning, not from a rule that one location is always better.

COUNT(*) counts result rows, including a LEFT JOIN placeholder. COUNT(t.id) counts non-null task IDs, so a person without tasks gets zero. GROUP BY builds one group per person here. Group by identity as well as name so two people with the same name are not merged. ORDER BY makes the demonstration's result order explicit.

Before you start

Use the MySQL 8.4 practice database from the keys lesson, not this application's database. SQL is identical on Windows/Ubuntu; Laragon/XAMPP are optional. Run SELECT DATABASE(), VERSION(); before continuing. The original fixture has three people and three tasks: Asha has done task 10 and todo task 11; Robin has todo task 12; Maya has none. If you completed the prior independent exercise, your fourth person/task changes the results. Compare the original three people, or create a new empty practice database and run the original setup there. Never reset another project to match this lesson.

Worked example: four reports

Run each SELECT separately on the original fixture:

SELECT p.name, t.id AS task_id, t.status
FROM study_people AS p
INNER JOIN study_tasks AS t ON t.person_id = p.id
ORDER BY p.id, t.id;

SELECT p.name, t.id AS task_id, t.status
FROM study_people AS p
LEFT JOIN study_tasks AS t ON t.person_id = p.id
ORDER BY p.id, t.id;

SELECT p.id, p.name, COUNT(t.id) AS task_count
FROM study_people AS p
LEFT JOIN study_tasks AS t ON t.person_id = p.id
GROUP BY p.id, p.name
ORDER BY p.id;

SELECT p.id, p.name, COUNT(t.id) AS done_count
FROM study_people AS p
LEFT JOIN study_tasks AS t ON t.person_id = p.id AND t.status = 'done'
GROUP BY p.id, p.name
ORDER BY p.id;

Expected results:

Aliases p and t shorten table references. The last query filters matching tasks in ON, then counts their non-null IDs. It keeps Robin and Maya even though neither has a done task. No query changes stored data.

Guided lab: find the missing people

1. Run all four queries and compare their ordered rows.
2. Change COUNT(t.id) to COUNT(*) in the all-task report. Observe Maya incorrectly shows 1 for a task-count requirement. Restore the original expression.
3. Move the done-status condition from ON into WHERE t.status = 'done' before GROUP BY. Expect only Asha. Explain why Robin and Maya disappear.
4. Add a new dummy person with a distinct ID/email but the same name as Asha. With no task, that person must have a separate zero-count row. Keep identity in GROUP BY.
5. In the disposable practice database, add a second task for Robin. Only Robin's all-task count should increase. Do not add DISTINCT merely to hide unexpected duplicates; inspect the relationship and join condition first.

Independent exercise

Create a todo-task count report that includes every person. For the original fixture, predict Asha 1, Robin 1, Maya 0. Write it before revealing hints. Then list only people with no tasks using a LEFT JOIN and WHERE t.id IS NULL. Predict Maya for the original fixture. If you changed the fixture, explain every extra result.

Common mistakes

• Forgetting the join condition and producing unrelated combinations.
• Using COUNT(*) for child counts after a LEFT JOIN.
• Grouping only by name and merging different people.
• Filtering the child in WHERE while expecting unmatched parents to remain.
• Assuming SQL gives a stable row order without ORDER BY.

Self-check — not graded

1. Why does Asha appear twice in the detailed join?
2. Which count gives Maya zero tasks?
3. Where can the done filter go to retain every person?

Interview practice

“A report loses customers with no orders. How would you investigate?” Inspect join type, cardinality, ON/WHERE conditions, NULL handling, and count expression. Compare with a tiny known fixture before measuring the real query. Add a regression case for a customer with zero orders.

English practice and glossary

• Join: combine related result rows.
• Alias: a short name for a table or expression.
• NULL: a missing/unknown SQL value.
• Aggregate: a calculation across grouped rows.
• Cardinality: how many rows match a relationship.

Write: “The original query excluded __ because __. I moved __ to __. The new result includes __ with a count of __.” Explain the business requirement as well as the SQL.

Recap and next step

Predict detailed rows before counting. Keep unmatched people deliberately, count child identities, and test filtering with a zero-task case. Next, use Laravel routing, validation and feature tests to expose a small report safely. Official references below were checked on 2026-10-22.