13. Joins, zero counts and cardinality

qatestingtest-designindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

13. Joins, zero counts and cardinality

Concept and contract

Check what each row represents before trusting an aggregate.

A join combines matching rows and can multiply them. Before checking a count, define whether it represents companies, roles or joined records. A test can return plausible numbers while counting the wrong entity, especially when multiple one-to-many relationships are joined together.

Use a tiny fixture that includes a company with no roles and a company with several roles. Those cases expose missing zero rows and accidental multiplication more clearly than a dataset where every company has exactly one posting.

Mechanism and worked example

The worked left join preserves both companies and counts r.id, which is absent for the unmatched company.

The worked left join preserves both companies and counts r.id, which is absent for the unmatched company. The expected counts are two for A and zero for B. Counting every joined row with COUNT(*) would report one for B instead.

Grouping and ordering belong to the contract. A company row should not disappear merely because its role count is zero. If additional tables such as tags are joined, reconsider whether distinct role ids or a separate aggregation is necessary.

Complete worked example

sql

CREATE TABLE companies(id TEXT PRIMARY KEY);
CREATE TABLE roles(id TEXT PRIMARY KEY, company TEXT);
INSERT INTO companies VALUES ('A'), ('B');
INSERT INTO roles VALUES ('r1','A'), ('r2','A');
SELECT c.id, COUNT(r.id) AS roles
FROM companies c LEFT JOIN roles r ON r.company=c.id
GROUP BY c.id ORDER BY c.id;

Reasoning: The left join preserves B while counting only matched role ids.

Counterexamples and common mistakes

The weak aggregate uses COUNT(*) after a left join.

The weak aggregate uses COUNT(*) after a left join. It counts the preserved unmatched company row, producing a misleading one. Successful SQL execution and a nonnegative count do not establish that the aggregate measures roles.

Do not apply DISTINCT automatically to hide every join problem. It can conceal a faulty relationship or remove legitimate duplicates. First identify the intended row identity, then choose a query structure that preserves it.

Counterexample: what this check misses

sql

SELECT c.id, COUNT(*) AS roles
FROM companies c LEFT JOIN roles r ON r.company=c.id GROUP BY c.id;

Reasoning: COUNT(*) counts the unmatched preserved row as one.

Independent practice: small task

Build a query returning only companies with zero roles, using the same disposable fixture.

Build a query returning only companies with zero roles, using the same disposable fixture. Predict B before writing SQL. Preserve the company id rather than returning an unexplained aggregate number.

Then explain why a left join is useful here. Optional extension: add two tags per role and demonstrate how a naive join changes counts. Keep that multiplication experiment separate from the basic zero-role query.

Step 1: Create A with two roles and B with none. Step 2: Select the zero-role company explicitly.

Practice starter: solve before reading the solution

sql

-- Create the same two-company fixture.
-- TODO: return company ids with zero roles, ordered by id.

Reasoning: Predict the zero-role company before constructing the aggregate.

Hint 1: Count a matched role key, not every preserved row.
Hint 2: State which entity the aggregate measures.

Solution, tests and trade-offs

The solution groups preserved companies and uses HAVING COUNT(r.id) = 0.

The solution groups preserved companies and uses HAVING COUNT(r.id) = 0. Its expected row is B. The local verifier checks exact ids and separately compares the incorrect COUNT(*) result, so the error has an observable counterexample.

A query over three fixture rows cannot prove a production reporting pipeline correct. Its purpose is to establish cardinality reasoning and an independent expected result. Add real schema relationships when applying the technique elsewhere.

Practice solution and acceptance checks

sql

CREATE TABLE companies(id TEXT PRIMARY KEY);
CREATE TABLE roles(id TEXT PRIMARY KEY, company TEXT);
INSERT INTO companies VALUES ('A'), ('B');
INSERT INTO roles VALUES ('r1','A'), ('r2','A');
SELECT c.id FROM companies c LEFT JOIN roles r ON r.company=c.id
GROUP BY c.id HAVING COUNT(r.id)=0 ORDER BY c.id;

Reasoning: The HAVING predicate refers to the intended role identity, not all joined rows.

Interview reasoning and next step

In interviews, explain why COUNT(*) and COUNT(nullable_column) differ after a left join.

In interviews, explain why COUNT(*) and COUNT(nullable_column) differ after a left join. State the entity being counted and show the zero case. Avoid treating every duplicate as a data-cleanup problem before inspecting the join.

Next, manage fixtures across tests so the same small dataset remains reproducible and one test cannot silently change another test’s starting conditions.

Optional stretch — after the core task works: Add a tag join and explain multiplicative rows.

Computer practice: download the companion lab at https://applydjinn.com/typorithm/qa-lab-20261105.zip. Read its README, install the pinned tools and run the accepted examples before attempting a starter. The mobile reader preserves your reading and self-check progress; the local lab runs browser tests separately.

Check what each row represents before trusting an aggregate.