SQL Joins SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL live · 2 guided

Departments with Their Employees (LEFT JOIN — Empty Groups Visible)

Return every department and its employee details, using NULL employee fields for empty departments.

  • Joins
  • CASE expressions
  • NULL handling
  • Sorting

Exercise brief

Understand the request

Workforce planning analyst A capacity roster must keep departments visible even when no employee is assigned.

Show every department alongside each of its employees. Departments with zero employees still appear (with NULL employee fields). Return department_id, department_name, location, employee_id, first_name, last_name, salary — ordered by department_id, salary DESC NULLS LAST, employee_id.

Return

  • Return department_id, department_name, location, employee_id, first_name, last_name, and salary.
  • Order deterministically by department and employee salary.

Constraints

  • Start from departments and preserve it with LEFT JOIN.

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
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • salaryINTEGER
  • department_idINTEGER

Hints, when you need them

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

Hint 1

Operations dept (id 50) has no employees — LEFT JOIN gives it ONE row with all employee columns NULL. The result has 11 rows, not 10.

Hint 2

Sorting NULLs is engine-specific: some put NULLs first, some last. The CASE-WHEN-IS-NULL trick forces explicit "NULLs last" portably.

Hint 3

Group by department_id first (primary sort), then salary DESC within department.

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, e.employee_id, e.first_name, e.last_name, e.salary FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, CASE WHEN e.salary IS NULL THEN 1 ELSE 0 END, e.salary DESC, e.employee_id;

Why this works

LEFT JOIN's promise: 'every left row, at least once'. Empty right-side groups still emit a row — useful for reporting (counts, completeness checks) and for spotting orphan/empty buckets.

Success check

All departments remain visible and Operations has one NULL-padded employee row.

Expected result

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

department_iddepartment_namelocationemployee_idfirst_namelast_namesalary
10ITSan Francisco100JohnSmith120000
10ITSan Francisco107GraceWhite90000
10ITSan Francisco101AliceJohnson85000
10ITSan Francisco102BobWilson80000
20HRNew York103CarolDavis60000
20HRNew York108HenryClark55000
30FinanceChicago104DavidBrown70000
30FinanceChicago109IvyMartinez68000
40MarketingLos Angeles105EmmaTaylor95000
40MarketingLos Angeles106FrankGreen65000

Previewing 10 of 11 expected rows. Run the query in the editor to inspect the full result.

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.