Users and Their Completed Sessions — NULL Handling in Joins in SQL

The problem

Streamhub's product team wants a list of every user alongside their completed sessions — those with a recorded end time.

Write a query to return each user alongside any completed session they have.

Assumptions:

Completed sessions have a recorded ended_at; sessions still in progress have a missing ended_at.
Every user must appear. Users with completed sessions contribute one row per completed session. Users with no completed sessions contribute a single row with a missing session_id.

Output:

One row per user-completed-session pairing, plus one row per user with no completed sessions, with columns user_id and session_id.

Schema · analytics 5 tables

users

id integer

name text

email text

country text

plan text

signed_up_at timestamptz

is_active boolean

conversions

id integer

user_id integer

converted_at timestamptz

plan text

amount numeric

sessions

id integer

user_id integer

started_at timestamptz

ended_at? timestamptz

event_count integer

events

id integer

user_id integer

session_id? integer

event_type text

occurred_at timestamptz

properties? jsonb

periods

id integer

name text

start_month integer

end_month integer

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  u.id AS user_id,
  s.id AS session_id
FROM
  users u
  LEFT JOIN sessions s ON u.id = s.user_id
  AND s.ended_at IS NOT NULL

The shape

Putting s.ended_at IS NOT NULL in the ON clause restricts which sessions attach to each user without dropping users from the result. The LEFT JOIN keeps every user, and the extra ON condition simply narrows the attaching population to completed sessions only.

Clause by clause

SELECT u.id AS user_id, s.id AS session_id returns each user alongside the ID of any completed session attached on that row. A user with no completed session has s.id missing — exactly how the report signals "no completed sessions on record."
FROM users u LEFT JOIN sessions s ON u.id = s.user_id AND s.ended_at IS NOT NULL pairs each user with their completed sessions. The ON clause now has two conjoined conditions: the user-to-session link, and the completed-status restriction. A session attaches only when both are true; otherwise the user still appears, with every s.* column missing. Users 61 through 80 in the result set illustrate this — they had no completed sessions, so each appears once with session_id missing.

Why this and not `WHERE s.ended_at IS NOT NULL`

A WHERE s.ended_at IS NOT NULL would drop every user whose sessions are all in-progress, and every user with no sessions at all. The in-progress sessions have s.ended_at missing, and NULL IS NOT NULL is false, so those rows fail the filter. The users with no sessions have a missing s.ended_at from the unmatched-row placeholder, and they fail the filter too. The result would collapse to a strict inner join filtered to completed sessions, which is not what the product team asked for.

The trap

The condition s.ended_at IS NOT NULL reads like a row filter, and in a WHERE clause it would be one. Inside an ON clause it is a join condition instead: it controls which right-side rows are eligible to attach, not which output rows survive. The placement decides whether unmatched users stay or disappear, and the predicate text alone gives no hint of that — only its position in the query does.

You practiced an ON-clause condition that tests a possibly-missing column — restrict which related rows attach while preserving every left record.