Find Orphan Account References Safely
Use NOT EXISTS to return records whose non-NULL account_id has no matching quality_accounts row.
- Joins
- Subqueries
- Filtering
- Sorting
Exercise brief
Understand the request
Referential-integrity analyst A load bypassed foreign-key enforcement and may contain account IDs with no parent account.
A load bypassed foreign-key enforcement and may contain account IDs with no parent account. Use NOT EXISTS to return records whose non-NULL account_id has no matching quality_accounts row.
Return
- Return record_id, customer_key, and account_id.
- Order by record_id.
Constraints
- Use a correlated NOT EXISTS anti-join.
- Do not classify NULL account_id values as orphans.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
quality_records
record_idINTEGERcustomer_keyTEXTaccount_idINTEGER
quality_accounts
account_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
An orphan has a supplied child key but no matching parent row.
Hint 2
Correlate the parent lookup to the current account_id.
Hint 3
Require q.account_id IS NOT NULL and NOT EXISTS the matching account.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT q.record_id, q.customer_key, q.account_id FROM quality_records q WHERE q.account_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM quality_accounts a WHERE a.account_id = q.account_id) ORDER BY q.record_id;Why this works
NOT EXISTS expresses referential absence directly and avoids the NULL-sensitive behavior that can make NOT IN return no rows.
Success check
Only the record referencing account 99 is returned, regardless of NULLs in the parent-key domain.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| record_id | customer_key | account_id |
|---|---|---|
| 1008 | C400 | 99 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Open the interactive workspace and practice across SQL topics.