Exclude Jobs Safely When Rules Contain NULL
Find employees whose job code does not match any exclusion rule.
- Subqueries
- Filtering
- Sorting
Exercise brief
Understand the request
Data governance analyst An access review excludes job codes held in a rule table that may contain NULL.
Return employees whose job code does not match any value in exclusion_rules.
Return
- Return first_name, last_name, job_id in this exact left-to-right order.
Constraints
- Use a NULL-safe anti-existence predicate instead of NOT IN against the nullable rule table.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
first_nameVARCHAR(50)last_nameVARCHAR(50)job_idVARCHAR(20)
exclusion_rules
blocked_job_idVARCHAR(20)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A nullable exclusion source makes `NOT IN` unsafe because SQL's three-valued logic can turn every comparison into UNKNOWN.
Hint 2
Use a correlated anti-existence check that asks whether any rule row matches the current employee's job code.
Hint 3
SELECT e.first_name, e.last_name, e.job_id FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM exclusion_rules r WHERE r.blocked_job_id = /* outer job code */ ) ORDER BY e.first_name;
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT e.first_name, e.last_name, e.job_id FROM employees e WHERE NOT EXISTS (SELECT 1 FROM exclusion_rules r WHERE r.blocked_job_id = e.job_id) ORDER BY e.first_name;Why this works
The exclusion table intentionally contains a NULL row. A naïve `job_id NOT IN (SELECT blocked_job_id ...)` therefore evaluates to UNKNOWN for otherwise unmatched jobs and can return no employees at all. The correlated `NOT EXISTS` anti-join is NULL-safe because only an actual equality match excludes the outer row. IT_PROG and CONTRACTOR are removed, while HR_REP and SALES_REP remain. This anti-existence pattern is portable across the supported engines and is the production-safe default for nullable rule tables.
Success check
No returned employee has a matching non-NULL exclusion rule, and the nullable rule does not erase valid rows.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| first_name | last_name | job_id |
|---|---|---|
| Chris | Anderson | SALES_REP |
| Emma | Thomas | HR_REP |
| Jane | Smith | HR_REP |
| Lisa | Davis | HR_REP |
| Sarah | Williams | SALES_REP |
| Tom | Wilson | SALES_REP |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Date Operations & Time-Based Analytics
Practice date arithmetic, safe timestamp ranges, calendar bucketing, dense time series, rolling windows, growth, and cohort analysis.
Open the interactive workspace and practice across SQL topics.