Watch 10 video solutions for Employee Bonus, a easy level problem involving Database. This walkthrough by Learn With Chirag has 12,653 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Employee
+-------------+---------+ | Column Name | Type | +-------------+---------+ | empId | int | | name | varchar | | supervisor | int | | salary | int | +-------------+---------+ empId is the column with unique values for this table. Each row of this table indicates the name and the ID of an employee in addition to their salary and the id of their manager.
Table: Bonus
+-------------+------+ | Column Name | Type | +-------------+------+ | empId | int | | bonus | int | +-------------+------+ empId is the column of unique values for this table. empId is a foreign key (reference column) to empId from the Employee table. Each row of this table contains the id of an employee and their respective bonus.
Write a solution to report the name and bonus amount of each employee with a bonus less than 1000.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Employee table: +-------+--------+------------+--------+ | empId | name | supervisor | salary | +-------+--------+------------+--------+ | 3 | Brad | null | 4000 | | 1 | John | 3 | 1000 | | 2 | Dan | 3 | 2000 | | 4 | Thomas | 3 | 4000 | +-------+--------+------------+--------+ Bonus table: +-------+-------+ | empId | bonus | +-------+-------+ | 2 | 500 | | 4 | 2000 | +-------+-------+ Output: +------+-------+ | name | bonus | +------+-------+ | Brad | null | | John | null | | Dan | 500 | +------+-------+
Problem Overview: You are given two tables: Employee and Bonus. The task is to return each employee's name and bonus where the bonus is less than 1000 or the employee has no bonus record at all. The tricky part is correctly handling employees whose bonus value is NULL.
Approach 1: SQL Join with NULL Handling (O(n + m) time, O(1) space)
This approach joins the Employee table with the Bonus table using a LEFT JOIN. A left join keeps every employee row even if no matching bonus record exists. After joining, filter rows where bonus < 1000 or bonus IS NULL. The key insight: employees without bonus entries appear with a NULL value after the join, which allows you to include them with the IS NULL condition. The database scans both tables once and performs a join operation, giving an overall time complexity of O(n + m) where n is employees and m is bonuses.
This method is straightforward and commonly used when combining relational data. If you're practicing SQL queries or working with relational datasets, mastering joins is essential.
Approach 2: SQL Subquery with COALESCE (O(n + m) time, O(1) space)
This version avoids joins by using a correlated subquery to fetch the bonus for each employee. The subquery retrieves the bonus from the Bonus table based on empId. Because employees without bonus rows return NULL, the query wraps the result with COALESCE (or IFNULL) to replace NULL with 0. Once converted, a simple comparison < 1000 filters the required rows.
The main idea is converting missing bonus values into a comparable number so the condition works consistently. Internally, the database still performs indexed lookups or scans similar to a join, resulting in roughly O(n + m) time complexity depending on indexing.
This approach is useful when you prefer compact queries or when the logic naturally fits a scalar lookup. It also demonstrates how SQL handles missing values in relational queries, a core concept in database problem solving.
Recommended for interviews: The LEFT JOIN solution is the expected answer. It clearly shows you understand relational joins and NULL filtering, both fundamental SQL interview concepts. The subquery approach also works but is usually considered secondary because joins are more idiomatic and easier for query optimizers to handle at scale.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| LEFT JOIN with NULL filtering | O(n + m) | O(1) | Best general solution when combining rows from two related tables |
| Subquery with COALESCE | O(n + m) | O(1) | When using scalar lookups or when avoiding explicit joins |
| LEFT JOIN with conditional filtering | O(n + m) | O(1) | Preferred in interviews and production SQL queries |
