Topic 47 of 52 · Beginner
Filter employees without losing missing salaries
Return employees whose salary is below 90 or unknown, ordered by ID.
MySQL 8-compatible SELECT syntax; sample queries also execute in SQLite 3. Window functions require MySQL 8+.
Problem statement
Return employees whose salary is below 90 or unknown, ordered by ID.
An eligibility review includes missing salary data for manual review.
Input: employees(id, dept, salary); salary may be NULL.
Output: Rows id, salary ordered by id.
- IDs are unique.
- Salary can be NULL; unknown salaries must be included.
Sample 1
Input: (1,100),(2,80),(3,NULL) Output: (2,80),(3,NULL)
Both low and missing salaries qualify.
Sample 2
Input: (1,100) Output: no rows
A known salary at or above the limit is excluded.
Brute-force solution
Two disjoint SELECTs can be combined with UNION ALL, requiring two branches over the source.
Two scans plus output sorting.
CREATE TABLE employees(id INTEGER PRIMARY KEY, dept VARCHAR(10), salary INTEGER);
INSERT INTO employees VALUES (1,'A',100),(2,'A',100),(3,'A',80),(4,'B',60),(5,'B',NULL);
SELECT id,salary FROM employees WHERE salary < 90 UNION ALL SELECT id,salary FROM employees WHERE salary IS NULL ORDER BY id;Optimized solution
Use one predicate with explicit NULL handling; no join or aggregate is needed.
- Read the table.
- Keep salary < 90 OR salary IS NULL.
- Sort by id.
CREATE TABLE employees(id INTEGER PRIMARY KEY, dept VARCHAR(10), salary INTEGER);
INSERT INTO employees VALUES (1,'A',100),(2,'A',100),(3,'A',80),(4,'B',60),(5,'B',NULL);
SELECT id, salary FROM employees WHERE salary < 90 OR salary IS NULL ORDER BY id;Complexity analysis
Time: O(n log n) worst case with sorting; index plans may avoid a separate sort.
Auxiliary space: O(n) worst-case sort workspace.
Test cases
- Empty table.
- Salary exactly 90 is excluded.
- All salaries NULL.
Expected rows from the supplied setup: (3,80), (4,60), (5,NULL).Common mistakes
- Using salary = NULL.
- Omitting the OR branch.
Key takeaways
- Specify NULL behavior.
- Boundary conditions matter.
- Keep output deterministic.
Follow-up challenges
- How would an index change the plan?
- Should missing salary be a separate status?