SQL COUNT, NULL and LEFT JOIN: Avoid Three Common Reporting Mistakes

Write the question before the query

Create fictional learners Asha, Bharat and Charu. Give Asha two submission rows, Bharat one and Charu none. There are three learners and three submissions, but those equal totals describe different things.

Ask three separate questions: How many learners exist? How many submissions exist? How many learners submitted at least one project? The expected answers are three, three and two. Record them before running SQL.

Distinguish rows from non-missing values

COUNT(*) counts input rows. COUNT(expression) counts rows where the expression is not NULL. A blank string is not automatically NULL, and an identifier with a missing value should be treated according to the dataset's rules.

Do not replace every missing value with zero before understanding its meaning. 'No score recorded' and 'Scored zero' communicate different information. A transformation that removes that distinction can change the interpretation of a report.

Inspect what a LEFT JOIN actually returns

A LEFT JOIN retains learners without a matching submission. For Charu, the learner columns remain present and the submission columns are NULL. Counting the joined rows for each learner would still give Charu one row.

If the question is the number of matched submissions per learner, count a non-NULL submission identifier instead. First inspect the unaggregated joined rows so you can see how Asha appears twice and Charu receives an unmatched row.

Place filters with the question in mind

A condition on the right-hand table in a WHERE clause can remove unmatched rows from a LEFT JOIN. Whether that is correct depends on your question. To retain all learners while considering only selected submissions, review whether the condition belongs in the join condition.

Avoid memorising a universal rule that every filter belongs in ON or WHERE. Explain the result you intend, inspect the rows and then choose the clause that preserves that meaning.

Use explicit acceptance checks

• The learner table contains three distinct learner IDs.
• The raw submission table contains three submissions.
• The LEFT JOIN has four rows in this example.
• Counting matched submission IDs gives Asha 2, Bharat 1 and Charu 0.
• Counting distinct learners with a submission gives 2.
• A completion-status filter is tested with an unmatched learner.

Carry the reasoning into a dashboard

Keep the query, sample data and expected output together. Write a sentence defining each reported number. If another person cannot tell whether a figure counts learners or submissions, improve the label before making a chart.

Check the SQL dialect you are using when extending the exercise; functions and syntax can vary. For guided query practice, explore Mango Engineers' SQL learning path and confirm the current syllabus, prerequisites and batch.