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

Find Accounts Without Flags Using NULL-Safe NOT EXISTS

Return account_id and account_name for accounts with no matching row in subquery_account_flags.

  • Subqueries
  • Filtering
  • Sorting

Exercise brief

Understand the request

Risk operations analyst A clean-account export must exclude accounts with flags even though an imported flag row has a missing account ID.

Find every employee who has nobody reporting to them. Use NOT EXISTS (the production-grade, NULL-safe form). The Engine Notes call out the subtle NOT IN bug if you tried `WHERE employee_id NOT IN (SELECT manager_id FROM employees)`. Return employee_id, first_name, last_name — ordered by employee_id.

Return

  • Return one row per unflagged account.
  • Order by account_id.

Constraints

  • Use correlated NOT EXISTS.
  • Do not use unguarded NOT IN; the NULL account_id in the flag table would make every comparison unknown.

Data you will use

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

subquery_accounts

  • account_idINTEGER
  • account_nameVARCHAR(50)

subquery_account_flags

  • flag_idINTEGER
  • account_idINTEGER
  • flag_reasonVARCHAR(50)

Hints, when you need them

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

Hint 1

An unguarded NOT IN becomes UNKNOWN for every candidate when its subquery returns any NULL.

Hint 2

Correlate each flag account_id to the outer account_id inside NOT EXISTS.

Hint 3

WHERE NOT EXISTS (SELECT 1 FROM subquery_account_flags f WHERE f.account_id = a.account_id)

Verified SQL answer

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

Reveal solution and explanation
SELECT a.account_id, a.account_name FROM subquery_accounts a WHERE NOT EXISTS (SELECT 1 FROM subquery_account_flags f WHERE f.account_id = a.account_id) ORDER BY a.account_id;

Why this works

NOT EXISTS tests whether a matching row exists and is not poisoned by unrelated NULL values. The orphan flag deliberately makes the superficially similar NOT IN form return no rows.

Success check

Accounts 1, 3, and 5 appear in account order; flagged accounts and the orphan flag do not.

Expected result

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

account_idaccount_name
1Atlas Works
3Cedar Health
5Elm Retail

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.