Join without multiplying mistakes | SQL: better questions for your data

The relationship determines the result

INNER JOIN retains combinations satisfying the condition. LEFT JOIN also retains unmatched left-hand rows, filling right-hand columns with NULL. A one-to-many relationship may produce several rows per entity; that is not inherently an error.

Filters that change meaning

In a LEFT JOIN, a WHERE condition requiring a right-table value may eliminate unmatched rows. Decide whether the condition belongs in ON matching or final filtering. Before counting or summing, check each row’s granularity.

Guided workplace application

Draw a sample with three teams: one with open incidents, one with closed incidents only, and one with no incidents. The dashboard needs all three. Put the status filter in the LEFT JOIN ON clause and count i.id, assuming it is a non-null key. Missing matches then give zero incidents. Putting the filter in WHERE removes preserved rows; adding OR i.id IS NULL also fails to recover the team whose closed incidents already matched in the JOIN. To show only customers having orders without order details, consider EXISTS. Before summing amounts, describe the grain: order, order line, or customer. A correct join can legitimately multiply rows, but the total must still represent the intended entity.

SELECT teams.name, COUNT(incidents.id) AS open_count
FROM teams
LEFT JOIN incidents ON incidents.team_id = teams.id
 AND incidents.resolved_at IS NULL
GROUP BY teams.id, teams.name

IN PRACTICE

To show teams with no open incidents, preserve the left-side teams and place the incident filter in the matching condition.

Common pitfalls

Moving filters without testing unmatched teams; confusing rows with entities.

Related topics: Count and group carefully · Changes that preserve rules

Take this idea with you

Validate cardinality and conditions before interpreting JOIN totals.

Create account

Reference: PostgreSQL 18: Table expressions · PostgreSQL 18 reference semantics; DR SQL 2026.2