Employee Bonus - Solution & Explanation
Problem Statement
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 | +------+-------+
Approach Overview
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 1: Approach 1: SQL Join with NULL Handling
This approach uses SQL to join the Employee and Bonus tables. We perform a left join between the Employee table and the Bonus table using the empId column. The goal is to filter employees who have a bonus less than 1000 or have no bonus (NULL). This utilizes SQL's handling of null values effectively.
The SQL query performs a LEFT JOIN on the Employee and Bonus tables. This yields all employees, and for those without a corresponding bonus entry, the bonus will be NULL. The WHERE clause filters the result set down to employees whose bonus is less than 1000 or NULL.
Code
SQL
Complexity
Time Complexity: O(N), where N is the number of employees because we are essentially making a single pass over each table.
Space Complexity: O(N) due to the storage needed for the result set of employees and their bonuses.
Approach 2: Approach 2: SQL Subquery with COALESCE
This approach uses a subquery with the COALESCE function to handle potential null values when checking bonus amounts. The COALESCE function allows the evaluation of potential nulls to a specific value before filtering with a WHERE clause.
This query introduces the COALESCE function which converts a NULL bonus to 0, making logical sense when comparing bonuses against 1000. A LEFT JOIN is used to ensure all Employee records are considered, making this slightly different in approach but similar in result to the previous solution.
Code
SQL
Complexity
Time Complexity: O(N) as similarly, it requires iterating over joined data which corresponds with Employee records.
Space Complexity: O(N) for the resulting dataset containing eligible employees.
Approach 3: SQL Approach using LEFT JOIN
This approach involves using a LEFT JOIN operation to combine the Employee and Bonus tables based on the empId column. A LEFT JOIN is suitable because it will include all employees, and match bonus records where available. A COALESCE function is used to replace NULL bonus values with 0 for comparison purposes.
This query performs the following actions:
- LEFT JOINs the Employee table with the Bonus table on the empId column.
- Uses COALESCE to handle NULL values by converting them to 0.
- Filters the results to include only those records where the bonus is less than 1000.
Code
SQL
Complexity
Time Complexity: O(n + m) where n is the number of records in the Employee table and m is the no. of records in the Bonus table.
Space Complexity: O(n), for storing the result set.
Approach 4: SQL Approach using Subquery
This approach makes use of a subquery to achieve the desired outcome. Here, for each row in the Employee table, the subquery checks the Bonus table and retrieves the bonus if it's available and below 1000.
This query involves a subquery that:
- For each employee, attempts to retrieve the bonus value if it meets the criteria of being less than 1000.
- Ensures that if no matching bonus record exists, it will use a null value, thus still allowing the employee's name to appear in the results.
Code
SQL
Complexity
Time Complexity: O(n * m), where n is the number of entries in the Employee table and m is the number of entries in the Bonus table, due to the subquery for each row.
Space Complexity: O(n), for storing the result set.
Approach 5: Left Join
We can use a left join to join the Employee table and the Bonus table on empId, and then filter out the employees whose bonus is less than 1000. Note that the employees with NULL bonus values after the join should also be filtered out, so we need to use the IFNULL function to convert NULL values to 0.
Code
MySQL
Complexity Comparison
| Approach | Complexity |
|---|---|
| Approach 1: SQL Join with NULL Handling | Time Complexity: O(N), where N is the number of employees because we are essentially making a single pass over each table. Space Complexity: O(N) due to the storage needed for the result set of employees and their bonuses. |
| Approach 2: SQL Subquery with COALESCE | Time Complexity: O(N) as similarly, it requires iterating over joined data which corresponds with Employee records. Space Complexity: O(N) for the resulting dataset containing eligible employees. |
| SQL Approach using LEFT JOIN | Time Complexity: O(n + m) where n is the number of records in the Employee table and m is the no. of records in the Bonus table. Space Complexity: O(n), for storing the result set. |
| SQL Approach using Subquery | Time Complexity: O(n * m), where n is the number of entries in the Employee table and m is the number of entries in the Bonus table, due to the subquery for each row. Space Complexity: O(n), for storing the result set. |
| Left Join | — |
Detailed Complexity Analysis
| 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 |
Video Solution
Employee Bonus | Leetcode 577 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 13,959 views views
Watch 9 more video solutions →Frequently Asked Questions
Is Employee Bonus easy or hard?
Employee Bonus Python/Java solution
How to solve Employee Bonus in O(n)?
What is the best approach for Employee Bonus?
Is Employee Bonus asked at Google/Amazon/Meta?
What data structure is used in Employee Bonus?
What is the time complexity of Employee Bonus?
Ready to solve this problem?
Practice Employee Bonus with our built-in code editor and test cases.
Practice on FleetCode