Sponsored
Sponsored
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 JOINThis 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 bThis 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: