Retiago Drago

Posted on

# Employee Bonus | LeetCode | MSSQL

## The Problem

The challenge involves two tables, `Employee` and `Bonus`.

`Employee` table:

empId (PK) name supervisor salary
int varchar int int

`Bonus` table:

empId (PK, FK) bonus
int int

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.

## Explanation

Here's an example for better understanding:

Input:

`Employee` table:

empId name supervisor salary
1 John 3 1000
2 Dan 3 2000
4 Thomas 3 4000

`Bonus` table:

empId bonus
2 500
4 2000

Output:

name bonus
John null
Dan 500

Brad, John, and Dan either have no bonus or a bonus less than 1000.

## The Solution

We'll explore two SQL solutions that solve this problem with subtle differences. We'll discuss their differences, strengths, weaknesses, and structures.

### Source Code 1

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.

### Source Code 2

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.

## Conclusion

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.