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.
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.
SQL
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.
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.
SQL
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.
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:
SQL
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.
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:
SQL
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.
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.
MySQL
| 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 | — |
| 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 |
Employee Bonus | Leetcode 577 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 12,653 views views
Watch 9 more video solutions →Practice Employee Bonus with our built-in code editor and test cases.
Practice on FleetCode