LeetCode 577. Employee Bonus

Problem

LeetCode SQL Problem

577. Employee Bonus

Employee table

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

Bonus table

empId	bonus
2	500
4	2000

Solution - LEFT JOIN

MySQL

Select all employee’s name and bonus whose bonus is < 1000

SELECT E.name
	,B.bonus
FROM Employee AS E
LEFT JOIN Bonus AS B ON E.empId = B.empId
WHERE B.bonus < 1000
	OR B.bonus IS NULL

TSQL

Select all employee’s name and bonus whose bonus is < 1000

SELECT E.name
	,B.bonus
FROM Employee AS E
LEFT JOIN Bonus AS B ON E.empId = B.empId
WHERE B.bonus < 1000
	OR B.bonus IS NULL

The above SQL query retrieves information from two tables, "Employee" and "Bonus", using a LEFT JOIN operation. It filters the results based on certain conditions. Let's break down the query step by step:

1. SELECT E.name, B.bonus: This part of the query specifies the columns you want to retrieve from the tables. It selects the "name" column from the "Employee" table (aliased as "E") and the "bonus" column from the "Bonus" table (aliased as "B").

2. FROM Employee AS E: This part indicates that the query is fetching data from the "Employee" table and gives it an alias "E" for easier reference in the rest of the query.

3. LEFT JOIN Bonus AS B ON E.empId = B.empId: This is a LEFT JOIN operation that combines records from the "Employee" table (aliased as "E") and the "Bonus" table (aliased as "B"). The join is performed based on the condition that the "empId" column in the "Employee" table matches the "empId" column in the "Bonus" table. This means that for each employee, if there is a matching bonus record, it will be included in the result set. If there is no matching bonus record, the "bonus" column will contain NULL.

4. WHERE B.bonus < 1000 OR B.bonus IS NULL: This part specifies the conditions for filtering the results. It includes two conditions joined by the OR operator:

   • B.bonus < 1000: This condition filters rows where the "bonus" value in the "Bonus" table is less than 1000.
   • B.bonus IS NULL: This condition filters rows where the "bonus" value in the "Bonus" table is NULL, meaning there is no bonus record for that employee.

The combination of the LEFT JOIN operation and the WHERE conditions ensures that the query retrieves employee names and their corresponding bonus amounts if the bonus is less than 1000 or if no bonus record exists for the employee. This can help identify employees who either have low bonuses or no bonuses at all.

Query Output

name	bonus
John	null
Dan	500
Brad	null