Helix Systems' HR team is auditing career progression and needs a list of every employee alongside any job history entry where the role held was 'Account Executive'.
Write a query to return each employee alongside the title of any matching job history entry.
Assumptions:
- A matching entry is one whose
titleis exactly'Account Executive'. - Every employee must appear. Employees with no matching entry contribute a single row with a missing
title. No current Helix Systems employee has an'Account Executive'entry on record, so every output row will have a missingtitle.
Output:
- One row per employee, with columns
nameandtitle.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
e.name,
jh.title
FROM
employees e
LEFT JOIN job_history jh ON e.id = jh.employee_id
AND jh.title = 'Account Executive'The shape
The 'Account Executive' predicate sitting in the ON clause is what guarantees every employee appears in the result. The matching rule never holds for any employee in the data — no one currently at Helix Systems has an 'Account Executive' entry on record — so every output row has title missing, but every employee still has their row.
Clause by clause
SELECT e.name, jh.titlereturns each employee's name alongside the title of any matching job-history entry. On this data,jh.titleis missing on every output row because the join attaches nothing for any employee.FROM employees e LEFT JOIN job_history jh ON e.id = jh.employee_id AND jh.title = 'Account Executive'pairs each employee with their'Account Executive'entries. TheONclause carries two conjoined conditions: the employee-to-history link, and the title restriction. Hope Ivers, Lane Moore, and Mark Nash all have a matched row attached (theirtitlereads'Account Executive'); every other employee appears withtitlemissing because no qualifying history entry exists.
Why this and not WHERE jh.title = 'Account Executive'
A WHERE filter on jh.title would collapse the result to only the three employees with an 'Account Executive' entry. The other employees — those with no qualifying entry — would have jh.title set to missing by the unmatched-row placeholder, and NULL = 'Account Executive' is not true. Those rows would fail the filter. The audit asked for every employee, so the predicate has to stay in ON where it controls matching rather than in WHERE where it controls survival.
The trap
When the right-side condition never holds for anyone, the ON-versus-WHERE distinction is the only thing standing between a full-employee output and an empty one. The ON version returns one row per employee with title uniformly missing — the audit signal that nobody currently at Helix has an 'Account Executive entry. The WHERE version would return an empty result, which looks like a query that produced nothing rather than an audit that produced a clear negative finding. Picking the right placement is what turns a no-match scenario from "no output" into "every left row, all with missing right-side" — and the second is what the audit needed.
You practiced moving an equality condition into the ON clause — when the right-side value never matches anywhere, every left record still appears with the right columns uniformly missing.
