Employee Bonus | Leetcode 577 | Crack SQL Interviews in 50 Qs

Welcome to Chapter 11 of the SQL Interview Series! In this chapter, we explore Leetcode Problem 577 (Employee Bonus). This problem evaluates your ability to combine multiple database relations while properly handling missing values (NULL data) using appropriate table joins and filtering logic.

1. Problem Statement & Objectives

We are given two relational database tables: Employee and Bonus. Our core objective is to retrieve specific employee details based on their bonus criteria.

The Input Tables

1. Employee Table:

   • empId (Primary Key): Unique identifier for each employee.
   • name: Name of the employee.
   • supervisor: Manager's employee ID.
   • salary: Employee's salary.
   • Role in query: Acts as the primary source table containing all employee records.

2. Bonus Table:

   • empId (Foreign Key / Unique Column): Identifier linking back to the Employee table.
   • bonus: Financial bonus assigned to the employee.
   • Role in query: Contains bonus records that may or may not exist for every employee in the Employee table.

Target Output

• Report the name and bonus amount of each employee.
• Filter Condition: The bonus must be less than 1000, OR the employee must have no bonus record at all (i.e., NULL bonus).
• Ordering: The result can be returned in any order.

2. Core Mental Model & Real-Life Analogy

Real-Life Analogy: The Company Payroll Audit

Imagine you are an HR auditor holding a master roster list of all active employees (the Employee table). Next to you, you have a separate envelope containing performance bonus slips (the Bonus table) submitted by the finance department.

• Not every employee received a bonus slip (some employees have no entry in the finance envelope).
• Some employees received a bonus less than $1000.
• Top performers received $1000 or more.
• The Task: Go down your complete master roster line by line. For each employee, check if their bonus slip exists and is under $1000. If an employee has no bonus slip in the envelope, do not skip them; instead, note their bonus as missing (NULL) and include them in the final audit report because they meet the criteria of "not having a bonus over 1000."

3. Why Inner Join Fails (The Status Quo Trap)

If we naively use an INNER JOIN between Employee and Bonus, SQL will only return rows where an empId exists in both tables simultaneously.

Consequences of Inner Join:

• Employees with empId 1 and 3 do not have entries in the Bonus table. An INNER JOIN entirely drops these rows, filtering out employees whose bonus is NULL.
• This violates the problem requirement: "with a bonus less than 1000", which explicitly includes employees who received no bonus at all (represented as NULL).

4. The Solution: Leveraging a Left Join

To ensure we examine every single employee from our primary table regardless of whether a matching bonus record exists, we must use a LEFT JOIN.

How LEFT JOIN Behaves Here:

• Matched Employee (e.g., empId = 2, Bonus = 500): Appends the bonus value to the employee's row.
• Unmatched Employee (e.g., empId = 1, Name = "Brad"): Since Brad has no entry in the Bonus table, SQL automatically inserts a NULL value for the bonus column.

5. Step-by-Step SQL Query Construction

Let's build the SQL query step by step using a modular approach.

Step 1: Establishing the Table Source and Join

We start with the FROM clause, designating Employee as our left table, aliased as e, and perform a LEFT JOIN with the Bonus table, aliased as b.

SELECT 
    e.name, 
    b.bonus
FROM Employee e
LEFT JOIN Bonus b
    ON e.empId = b.empId

Step 2: Adding the Filtering Conditions

We need to filter rows based on two distinct conditions using the WHERE clause:

1. The bonus is strictly less than 1000 (b.bonus < 1000).
2. The bonus is missing / NULL (b.bonus IS NULL).

Since an employee should be included if either condition is satisfied, we combine them using the OR logical operator.

WHERE b.bonus < 1000 OR b.bonus IS NULL

6. Complete Production Code

Here is the complete, clean SQL query that successfully passes Leetcode 577:

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;

Walkthrough of Example Data Execution

7. Real-World Practical Pitfalls & Edge Cases

When working with LEFT JOIN and NULL checks in production databases, keep these common pitfalls in mind:

• Mistaking NULL for =: Never write b.bonus = NULL. In SQL, NULL represents an unknown or missing value, so equality comparisons (=) always evaluate to unknown. Always use the IS NULL operator.
• Accidentally Filtering Out NULLs in the WHERE Clause: If you put a condition like WHERE b.bonus < 1000 without the OR b.bonus IS NULL clause, any row where b.bonus is NULL will be filtered out, because NULL < 1000 evaluates to unknown, effectively turning your LEFT JOIN into an INNER JOIN.
• Primary-Foreign Key Relationships: Ensure that joining columns (empId) share identical data types (e.g., both integers or both UUIDs) to prevent implicit type-casting performance hits during query execution.

8. Summary and Key Takeaways

• Problem Classification: This is a classic relational join problem testing LEFT JOIN mechanics and NULL value handling.
• Master Table Preservation: Use a LEFT JOIN when you must guarantee that every record from the left-hand table is preserved in the final output.
• Handling Missing Data: When a LEFT JOIN cannot find a match in the right table, it populates those columns with NULL values.
• IS NULL Operator: Always use IS NULL (never = NULL) when querying for missing or unassigned database records.
• Logical Operators (OR): Combine distinct business rules using OR when multiple valid states (e.g., bonus under 1000 OR no bonus record) should satisfy the selection criteria.
• Table Aliasing: Use concise table aliases (e.g., e for Employee, b for Bonus) to improve query readability and maintainability.