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 | +------+-------+
To solve #577 Employee Bonus, the goal is to list employees whose bonus is either less than 1000 or NULL. Since not every employee may have a corresponding record in the bonus table, the key idea is to use a LEFT JOIN between the Employee and Bonus tables.
A LEFT JOIN ensures that all employees from the main employee table are included, even if they do not have an entry in the bonus table. After joining, you can filter the results using a WHERE clause to keep rows where the bonus value is either below the threshold or missing. Handling NULL values correctly is important because employees without a bonus record should still appear in the output.
This approach relies on standard SQL join operations and conditional filtering. In most database systems, the performance depends on table scans and indexing on the join key (such as empId). The method is efficient and commonly used in interview-style SQL problems.
| Approach | Time Complexity | Space Complexity |
|---|---|---|
| LEFT JOIN with conditional filtering | O(n + m) depending on table scan and join efficiency | O(1) additional space (excluding result set) |
Learn With Chirag
Use these hints if you're stuck. Try solving on your own first.
If the EmpId in table Employee has no match in table Bonus, we consider that the corresponding bonus is null and null is smaller than 1000.
Inner join is the default join, we can solve the mismatching problem by using outer join.
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.
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.
1SELECT Employee.name, Bonus.bonus FROM Employee LEFT JOIN Bonus ON Employee.empId = Bonus.empId WHERE Bonus.bonus < 1000 OR Bonus.bonus IS NULL;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.
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.
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.
1SELECT Employee.name, COALESCE(Bonus.bonus, 0) AS bonus FROM Employee LEFT 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.
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.
1SELECT e.name, COALESCE(b.bonus, 0) AS bonus FROM Employee e LEFT 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.
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.
1SELECT e.name, (SELECT b.bonus FROM Bonus b WHERE e.empId = b.empId AND bWatch expert explanations and walkthroughs
Practice problems asked by these companies to ace your technical interviews.
Explore More ProblemsJot down your thoughts, approach, and key learnings
Yes, similar SQL problems are commonly asked in technical interviews to test database fundamentals. They evaluate your understanding of joins, filtering conditions, and handling missing data.
The optimal approach is to use a SQL LEFT JOIN between the Employee and Bonus tables. This ensures all employees are included even if they do not have a bonus record, and then you filter for bonuses less than 1000 or NULL values.
LEFT JOIN is used because some employees may not have entries in the Bonus table. Using LEFT JOIN guarantees those employees still appear in the result with NULL bonus values, which can then be filtered accordingly.
Key SQL concepts include joins (especially LEFT JOIN), handling NULL values, and filtering results with a WHERE clause. Understanding how relational tables connect through keys like empId is also essential.
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.
This query performs the following actions:
This query involves a subquery that: