Count-star counts rows; count-column counts non-null values. Distinct changes the counting target again.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: A left-joined parent without children contributes one output row but zero non-null child IDs. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Choose the counting target
Rows and non-null child identifiers are different quantities.
Step 2: Trace unmatched parents
A left join still creates one result row with null child columns.
Step 3: Count actual children
Use a non-null child key rather than count(*) for optional children.
Worked scenario
A left-joined parent without children contributes one output row but zero non-null child IDs.
SELECT c.id, COUNT(o.id) AS order_count
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;A customer with no orders receives zero. Count(*) would report one because the null-extended output row itself exists. Count(DISTINCT o.id) addresses duplication only when that matches the intended contract.
Common mistake
Count-star after a left join may report one child where none exists.
Verify the behavior
Test zero, one and several children, including another join that could duplicate them.
Interview exercise
Count optional children per parent.
Answer and reasoning
Count the child identifier and group by the parent, assuming that identifier is non-null for actual children.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.