SQL subquery with = vs IN: Why a multirow team filter misses employees

You write a query for “all employees on East-region teams.” There are two teams, but only one employee appears. The missing people did not change regions. The inner query's row count does not fit the outer comparison operator.

How many team IDs does the subquery return?

Use a small fixed dataset. East has Core and Data; West has Ops. Hana belongs to Core, Jin and Mira to Data, and Sol to Ops.

CREATE TABLE teams (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  region TEXT NOT NULL
);

CREATE TABLE staff (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  team_id INTEGER NOT NULL REFERENCES teams(id)
);

INSERT INTO teams (id, name, region) VALUES
  (1, 'Core', 'East'), (2, 'Data', 'East'), (3, 'Ops', 'West');

INSERT INTO staff (id, name, team_id) VALUES
  (11, 'Hana', 1), (12, 'Jin', 2),
  (13, 'Sol', 3), (14, 'Mira', 2);

Run the inner query on its own. It returns both 1 and 2:

SELECT id FROM teams WHERE region = 'East' ORDER BY id;
-- 1
-- 2

The parentheses will contain a list of values, not a guaranteed single value. Adding = outside without noticing that cardinality creates the bug.

Why does = return only one employee here?

SELECT name
FROM staff
WHERE team_id = (
  SELECT id FROM teams WHERE region = 'East' ORDER BY id
)
ORDER BY id;
-- Hana

This is the result in SQLite. When a scalar subquery returns multiple rows, SQLite uses the first row's value. With ORDER BY id, that value is team ID 1, so only Hana matches. SQLite's expression documentation defines this behavior; other database systems need not handle a multirow scalar subquery the same way.

The ORDER BY makes the first team predictable in this demonstration. Adding LIMIT 1 is not a fix when the requirement is every team in a region. Silently narrowing the result makes the query harder to debug.

Use IN to compare all East team IDs

In this dataset, = uses sorted team ID 1 and returns Hana. IN checks membership in IDs 1 and 2, returning Hana, Jin, and Mira.

SELECT name
FROM staff
WHERE team_id IN (
  SELECT id FROM teams WHERE region = 'East'
)
ORDER BY id;
-- Hana
-- Jin
-- Mira

IN asks whether team_id belongs to the inner result set. Duplicate occurrences of a team ID in that set would not duplicate Hana in this result; this is a membership condition, not a join that multiplies rows.

When should you choose = or IN?

• Use = when the condition is guaranteed to identify exactly one team ID, and check that a key or uniqueness constraint supports the assumption.
• Use IN when multiple team IDs are valid, as with all teams in one region.

If you change the region to North, which has no teams here, both queries return no employees in this dataset. Zero rows and multiple rows are different contract cases. If the requirement is “one team,” handle missing or duplicate teams explicitly. If it is “all teams in this region,” multiple rows are expected. Check the inner row count before hiding a problem with LIMIT 1.

Key takeaways

The crucial property of a subquery is how many rows it can return. East has two teams. In SQLite, using = in this scalar position uses the first sorted ID and finds only Hana; IN compares both and finds three employees. Run the inner query separately when results are unexpectedly sparse.