SQL Joins SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Anti-Join via NOT EXISTS — Employees Without Direct Reports

Return employees for whom no reporting employee exists.

  • Joins
  • Subqueries
  • Filtering
  • Sorting

Exercise brief

Understand the request

Succession planning manager A development programme targets individual contributors who currently have no direct reports.

Find every employee who is NOT a manager (i.e. nobody reports to them). Use NOT EXISTS. Return employee_id, first_name, last_name, department_name, job_title — ordered by department_id, employee_id.

Return

  • Return employee_id, first_name, last_name, department_name, job_title in this exact left-to-right order.

Constraints

  • Use correlated NOT EXISTS for NULL-safe anti-join semantics.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • job_idVARCHAR(20)
  • manager_idINTEGER
  • department_idINTEGER

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

jobs

  • job_idVARCHAR(20)
  • job_titleVARCHAR(100)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

EXISTS / NOT EXISTS only checks "does any row match?" — it doesn't care about column values returned. Use SELECT 1 by convention.

Hint 2

The correlated reference (`r.manager_id = e.employee_id`) ties the inner query to each outer row.

Hint 3

NOT IN (subquery) has a subtle NULL trap; NOT EXISTS is always safe.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT e.employee_id, e.first_name, e.last_name, d.department_name, j.job_title FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN jobs j ON e.job_id = j.job_id WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.employee_id) ORDER BY e.department_id, e.employee_id;

Why this works

Anti-join is a fundamental pattern: 'find rows in A that have NO match in B'. Used everywhere — orphan detection, find-the-missing, churn analysis. The NOT EXISTS form is the safest and most portable.

Success check

Every non-manager appears once and every employee with a direct report is excluded.

Expected result

Use this output to verify values, aliases, ordering, and row count.

employee_idfirst_namelast_namedepartment_namejob_title
101AliceJohnsonITSoftware Developer
102BobWilsonITSoftware Developer
107GraceWhiteITSoftware Developer
108HenryClarkHRHR Representative
109IvyMartinezFinanceFinancial Analyst
106FrankGreenMarketingSales Representative

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.