Employees in Two Departments — AND, OR, NOT in SQL

The problem

A project manager at Helix Systems is assembling a cross-functional team drawing from the Engineering and Marketing departments, which have department_id values 1 and 2 respectively.

Write a query to return the name and department ID of every employee assigned to either of those departments.

Assumptions:

The employees table contains every active and former employee at Helix Systems.
The department_id column links each employee to their department.

Output:

One row per qualifying employee, with columns name and department_id.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  name,
  department_id
FROM
  employees
WHERE
  department_id = 1
  OR department_id = 2

The shape

Two equality checks on department_id, joined by OR — the cross-functional roster pulls anyone sitting in either of the two named departments.

Clause by clause

SELECT name, department_id returns the person and the department they belong to. Keeping department_id in the output makes it easy to verify the filter at a glance — every row carries the code that earned its inclusion.
FROM employees reads the employees table. Every active and former employee lives here, and the filter does the narrowing.
WHERE department_id = 1 OR department_id = 2 qualifies a row when either equality is true. An Engineering employee passes on the first condition, a Marketing employee passes on the second, and an employee in any other department fails both and is dropped.

Why this and not `AND`

The two conditions test the same column against different values. A single employee's department_id can only equal one value at a time, so an AND between them would demand a row where department_id equals 1 and equals 2 simultaneously. No row can satisfy that, so the AND form would return zero rows every time. OR is the right operator any time the qualifying rule is "this column is one of these values."

You practiced using OR to combine two equality checks against the same column. Pulling rows that match any of a small set of IDs is the recurring shape behind every cross-functional or multi-segment audience.

Return the name and department ID of every employee assigned to either of those departments

The shape

Clause by clause

Why this and not `AND`

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not AND

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not `AND`