The challenge involves two tables,
|empId (PK, FK)||bonus|
The goal is to write an SQL query that reports the name and bonus amount of each employee who receives a bonus of less than 1000.
Here's an example for better understanding:
Brad, John, and Dan either have no bonus or a bonus less than 1000.
We'll explore two SQL solutions that solve this problem with subtle differences. We'll discuss their differences, strengths, weaknesses, and structures.
The first solution employs a LEFT JOIN to combine the two tables. It then filters out employees with a bonus of 1000 or more.
SELECT e.name, b.bonus FROM Employee e LEFT JOIN Bonus b ON e.empId = b.empId WHERE b.bonus IS NULL OR b.bonus < 1000
This solution has a runtime of 1347ms, outperforming 27.70% of other submissions.
The second solution closely resembles the first. The only difference is the order of conditions in the WHERE clause. It first checks for bonuses less than 1000 before checking for NULL bonuses.
SELECT e.name, b.bonus FROM Employee e LEFT JOIN Bonus b ON e.empId = b.empId WHERE b.bonus < 1000 OR b.bonus IS NULL
The runtime for this solution is 984ms, beating 88.51% of other submissions.
Both solutions yield the desired outcome, but the second solution performs better. Consequently, the ranking of solutions based on overall performance, from best to worst, is as follows: Source Code 2 > Source Code 1.
Your choice should depend on your specific requirements and performance expectations.
You can find the original problem at LeetCode.
For more insightful solutions and tech-related content, connect with me on my Beacons page.