Skip to main content

LeetCode 1978. Employees Whose Manager Left the Company SQL Solution

Problem​

LeetCode SQL Problem

  1. Employees Whose Manager Left the Company

Employees table

employee_idnamemanager_idsalary
3Mila960301
12Antonellanull31000
13Emerynull67084
1Kalel1121241
9Mikaelanull50937
11Joziah628485

Solution - Self Join + Left Join​

Employees Whose Manager Left the Company
SELECT E.employee_id
FROM Employees AS E
LEFT JOIN Employees AS M ON E.manager_id = M.employee_id
WHERE E.salary < 30000
	AND E.manager_id IS NOT NULL
	AND M.employee_id IS NULL

The above SQL query selects the employee IDs of employees who have a salary less than 30,000 and have a manager, but their manager is not present in the Employees table.

Let's break the SQL down step by step:

  1. SELECT E.employee_id: This selects the employee IDs of the employees we're interested in.

  2. FROM Employees AS E: This specifies the table we're selecting from, which is the Employees table, and assigns it an alias of "E".

  3. LEFT JOIN Employees AS M ON E.manager_id = M.employee_id: This joins the Employees table to itself using a left join. The join condition is that the manager ID of the employee in table E matches the employee ID in table M. This will create a table where each row represents an employee, and the corresponding row (if any) from the Employees table where that employee's manager ID matches the manager's employee ID.

  4. WHERE E.salary < 30000: This filters the results to only include employees with a salary less than 30000.

  5. AND E.manager_id IS NOT NULL: This adds another filter, requiring that the employee has a manager (i.e., their manager ID is not null).

  6. AND M.employee_id IS NULL: This final filter requires that the corresponding row from the Employees table (i.e., the row representing the employee's manager) is null. This means that the employee does not have a manager in the Employees table, so their manager is not present in the table.

The overall effect of this SQL query is to find employees with a salary less than 30000 who have a manager, but whose manager is not present (left the company) in the Employees table.

Query Output

employee_id
11