Users with No Sessions — LEFT JOIN and RIGHT JOIN in SQL

The problem

Streamhub's platform team is auditing user engagement and needs to identify accounts with no session activity.

Write a query to return the user ID and account plan for every user with no recorded sessions.

Assumptions:

The users table contains every account on the platform.
The sessions table contains every session; user_id links each session back to a user.
A user with zero sessions is one whose id does not appear in sessions.user_id.

Output:

One row per inactive user, with columns user_id and plan.

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,
  u.plan
FROM
  users u
  LEFT JOIN sessions s ON u.id = s.user_id
WHERE
  s.id IS NULL

The shape

The LEFT JOIN keeps every user; the WHERE s.id IS NULL filter then keeps only the users whose session-side columns came back as NULL — meaning the join found no matching session. The 20 rows in the result are exactly the accounts that have never opened a session.

Clause by clause

SELECT u.id AS user_id, u.plan returns the user's ID, aliased to user_id, and the user's plan. Both columns are read from the left table, which is intentional: the right table only contributes to the existence check, not to the output.
FROM users u LEFT JOIN sessions s ON u.id = s.user_id pairs each user with each of their sessions. Active users produce one row per session with real values in s.*. Inactive users produce a single row with NULL in every s.* column.
WHERE s.id IS NULL keeps only the inactive users. sessions.id is the primary key of the sessions table — a real session always has an id. So s.id IS NULL is unambiguous: the row was synthesised by the outer join to preserve an unmatched left-side user.

Why this and not `INNER JOIN`

INNER JOIN would return only users who do have sessions — the opposite of the question. The audit asks for accounts with no session activity, and INNER JOIN's match-only semantics throws those away before any WHERE clause can pick them out.

The LEFT JOIN is what makes the unmatched users visible in the first place. The WHERE s.id IS NULL then keeps only those visibly-unmatched rows. The two clauses work as a unit: without the join, there's nothing to filter; without the filter, you get every user, matched or not.

The trap

The trap is checking the wrong column for NULL. WHERE u.id IS NULL filters out every row — users.id is a primary key, never NULL on a real row. The query runs cleanly, returns zero rows, and looks like "no inactive users on the platform." The fix is to check a column on the right side of the join — whichever right-side column is guaranteed non-NULL for real rows, with the primary key being the conventional choice.

You practiced the anti-join pattern in a different domain. The structure is identical to 'customers with no orders' — only the table names change.