{x}
blog image

Employee Bonus

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   |
+------+-------+

Solution Explanation for LeetCode 577: Employee Bonus

This problem requires retrieving the name and bonus of employees from two tables: Employee and Bonus. The solution involves joining these tables and filtering the results based on the bonus amount.

Approach: LEFT JOIN and Filtering

The most efficient way to solve this is using a LEFT JOIN between the Employee and Bonus tables. A LEFT JOIN ensures that all rows from the left table (Employee) are included in the result, even if there's no matching row in the right table (Bonus). This handles cases where an employee doesn't have a bonus entry.

After joining, we filter the results to include only employees with a bonus less than 1000. Since some employees might not have a bonus (resulting in NULL values), we use a function like IFNULL (MySQL) to handle these NULL values, treating them as 0 for comparison.

SQL Code (MySQL)

SELECT name, bonus
FROM
    Employee
    LEFT JOIN Bonus USING (empId)
WHERE IFNULL(bonus, 0) < 1000;

Explanation:

  1. SELECT name, bonus: This selects the employee's name and bonus amount.
  2. FROM Employee LEFT JOIN Bonus USING (empId): This performs a LEFT JOIN between the Employee and Bonus tables using empId as the common column. USING (empId) is a shorthand for ON Employee.empId = Bonus.empId.
  3. WHERE IFNULL(bonus, 0) < 1000: This filters the results. IFNULL(bonus, 0) replaces NULL values in the bonus column with 0. The condition then ensures that only employees with a bonus less than 1000 (including those with no bonus) are included in the output.

Time and Space Complexity Analysis

  • Time Complexity: The time complexity is dominated by the LEFT JOIN operation. In the worst case, this operation has a time complexity of O(M * N), where M is the number of rows in Employee and N is the number of rows in Bonus. However, with optimized database indexing on empId in both tables, this complexity can be significantly reduced to O(M + N) or even close to O(log(M+N)) depending on the database's indexing strategy. The filtering step adds a linear time complexity of O(M), where M is the number of rows after the join. Overall, the dominant factor is likely to be the join.
  • Space Complexity: The space complexity is determined by the size of the result set, which is at most the number of rows in the Employee table. Therefore, the space complexity is O(M) in the worst case, where M is the number of employees. The space used by intermediate steps during the join operation is also dependent on the database's internal implementation and indexing.

Other SQL Dialects

The core logic remains the same for other SQL dialects like PostgreSQL, SQL Server, and Oracle. The only potential difference would be the function used to handle NULL values. For example:

  • PostgreSQL: Use COALESCE(bonus, 0) instead of IFNULL(bonus, 0).
  • SQL Server: Use ISNULL(bonus, 0) instead of IFNULL(bonus, 0).
  • Oracle: Use NVL(bonus, 0) instead of IFNULL(bonus, 0).

The rest of the query would remain identical, leveraging the LEFT JOIN and the < 1000 condition for filtering.