← SQL roadmap

Topic 47 of 52 · Beginner

Filter employees without losing missing salaries

Return employees whose salary is below 90 or unknown, ordered by ID.

BeginnerFreshers · 0 yearsFiltering

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.

  1. Read the table.
  2. Keep salary < 90 OR salary IS NULL.
  3. 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?
Practise SQL with the AI mentor

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.