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

Semi-Join via EXISTS — Departments That Have a Manager

Return each department for which an employee in that department has at least one direct report.

  • Joins
  • Subqueries
  • Filtering
  • Sorting
  • Distinct values

Exercise brief

Understand the request

Department planning analyst Leadership needs departments that contain at least one manager, without multiplying department rows by reports.

Find every department that contains AT LEAST ONE employee who has direct reports (i.e. is a manager). Use EXISTS. Return department_id, department_name, location — ordered by department_id.

Return

  • Return department_id, department_name, and location once per department.
  • Order by department_id.

Constraints

  • Use a correlated EXISTS semi-join.
  • Do not rely on DISTINCT to repair multiplied rows.

Data you will use

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

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)
  • locationVARCHAR(100)

employees

  • employee_idINTEGER
  • manager_idINTEGER
  • department_idINTEGER

Hints, when you need them

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

Hint 1

Semi-join keeps each LEFT row at most ONCE — even if many right-side rows match. EXISTS is the natural fit.

Hint 2

The original (legacy) version checked manager_id IS NULL — that finds top-level employees, not managers. A real "is a manager" check looks for OTHER employees pointing AT this one.

Hint 3

Two nested EXISTS is fine: outer says 'dept has some employee', inner says 'and that employee has reports'.

Verified SQL answer

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

Reveal solution and explanation
SELECT d.department_id, d.department_name, d.location FROM departments d WHERE EXISTS (SELECT 1 FROM employees mgr WHERE mgr.department_id = d.department_id AND EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = mgr.employee_id)) ORDER BY d.department_id;

Why this works

Semi-join's defining property: it filters the LEFT table; it does NOT add columns from the right and does NOT duplicate left rows. This is the conceptual fix to the original Q18 which conflated 'is a manager' (has reports) with 'has no manager' (top of tree).

Success check

Each qualifying department appears once regardless of its number of managers or reports.

Expected result

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

department_iddepartment_namelocation
10ITSan Francisco
20HRNew York
30FinanceChicago
40MarketingLos Angeles

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.