SQL Subqueries SQL Topic exerciseEasyVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Find Employees in West Coast Departments with IN

Use IN with a subquery that returns matching department IDs, then return the requested employee and department columns.

  • Joins
  • Subqueries
  • Filtering
  • Sorting

Exercise brief

Understand the request

Workplace operations analyst A regional roster needs employees assigned to departments in San Francisco, Los Angeles, or Seattle.

Find every employee in a department located on the West Coast — namely San Francisco, Los Angeles, or Seattle. Use IN against a subquery that returns the matching department ids. Return employee_id, first_name, last_name, department_name — ordered by department_name, employee_id.

Return

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

Constraints

  • Use IN (SELECT ...) for department membership.
  • Do not hard-code department IDs in the outer predicate.

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)
  • department_idINTEGER

departments

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

Hints, when you need them

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

Hint 1

IN (subquery) keeps outer rows whose key matches ANY of the inner result set.

Hint 2

The inner result set may have any number of rows — IN handles 0, 1, or many.

Hint 3

Equivalent to a semi-join via EXISTS — same result, often the same execution plan.

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 FROM employees e INNER JOIN departments d ON e.department_id = d.department_id WHERE e.department_id IN (SELECT department_id FROM departments WHERE location IN ('San Francisco', 'Los Angeles', 'Seattle')) ORDER BY d.department_name, e.employee_id;

Why this works

IN (subquery) is the most-asked subquery shape in interviews. It's clean, readable, and engine-portable. Beware NOT IN with a subquery containing NULLs — see Q15.

Success check

Every employee belonging to a qualifying location appears exactly once.

Expected result

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

employee_idfirst_namelast_namedepartment_name
100JohnSmithIT
101AliceJohnsonIT
102BobWilsonIT
107GraceWhiteIT
105EmmaTaylorMarketing
106FrankGreenMarketing

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.