Home
Microsoft SQL Server

Self Join Example - SQL Query to Find Employees Earning More Than Managers - LeetCode Solution

6

Write an SQL Query to Find Employees Earning More Than Managers

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id as shown below:

+----+-------+--------+-----------+

| Id | Name  | Salary | ManagerId |

+----+-------+--------+-----------+

| 1  | Joe   | 70000  | 3         |

| 2  | Henry | 80000  | 4         |

| 3  | Sam   | 60000  | NULL      |

| 4  | Max   | 90000  | NULL      |

+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager because Henry's Manager is Max who earns 90000 which is more than Henry's salary of 80000.

+----------+

| Employee |

+----------+

| Joe      |

+----------+

By the way, this is my fifth  article on solving LeetCode SQL Query problems, earlier I have solved the following SQL coding problems if you want you can check them as well:

  1. How to find duplicate emails?
  2. How to find customers who have never ordered?
  3. Generate a report to display Person and their Address
  4. How to find the Nth highest salary

These SQL problems are great way to build your SQL query skills, which is one of the essential skills to crack Coding interviews and go a long way in your career. I highly recommend every programmer and Data Scientist to spend some time learning SQL basics and practices SQL problems like this one to get their mind working as a SQL engine.

Once you pass through all these basic SQL query questions then you can try your hand on most complex ones which are given inSQL Puzzles and Answers, the 2nd Edition by Joe Celko, the SQL Wizard. This book contains some of the most difficult and exciting SQL problems and if you are serious about improving your SQL query skills, I highly recommend you to go through that book.

Btw, I expect that you have some experience on writing SQL query and you are familiar with essential SQL concepts like Joins, subquery, group by, aggregate functions like COUNT() function and things co-related sub-queries.

If you are not and just started with SQL then I highly recommend you go through a comprehensive SQL course likeThe Complete SQL Bootcamp  by Jose Portilla on Udemy. It's one of the best and also most affordable courses to learn SQL online.

Anyway, let's go back to our SQL query problem - How do you Find Employees Earning More Than Managers?

Solution - Using Self Join with the table itself

This problem can be solved by using Self Join. In Self Join, we join two instances of the same table to find a solution. In order to find all employees whose salary is greater than their manager, we first need to find employees who are a manager then we compare their salary to find out all employees who earn more than their manager.

To find out managers, we create two instances of Employee table e1 and e2 and compare e1.ManagerId = e2.Id to find all managers, the next condition is just to compare their salaries.

This shows the power of Self Join.  If you want to learn more about SQL Joints, I highly recommend you to check out the SQL for Data Science on Coursera . One of the best courses to learn SQL for beginners and you can access it for free if you don't need a certificate.

Self Join Example - SQL Query to Find Employees Earning More Than Managers - LeetCode Solution

Here is the SQL query to find Employees earning more than their mangers :

# Write your MySQL query statement below

SELECT e1.name FROM Employee e1 JOIN Employee e2 ON e1.ManagerId = e2.Id WHERE e1.salary > e2.salary

This is also my accepted answer in LeetCode. By the way, you can also write this query as

SELECT e2.name FROM Employee e1 JOIN Employee e2 ON e1.Id = e2.ManagerId WHERE e1.salary < e2.salary

The first one is more readable than the second one.

Let's see another example of Self Join in this table, this time you need to print the name of the employee and their manager :

SELECT e1.name, e2.name as Manager FROM Employee e1 JOIN Employee e2 ON e1.Id = e2.ManagerId

Last one, print all employee who are managers :

SELECT b.name FROM Employee a, Employee b WHERE a.ManagerId = b.Id

That's all about Self Join example in Database. You know it a great way to learn essential SQL concepts bb using Leetcode database problem of finding employees earning more than their managers. LeetCode also has a good collection of SQL problems which are good to improve your SQL query skills and I suggest you take a look at those problems if you want to improve your SQL query skills.

Further Learning

The Complete SQL Bootcamp

Introduction to SQL

SQL Puzzles and Answers, the 2nd Edition

SQL for Data Science on Coursera

Other related SQL Articles and Tutorials you may like

  • 10 Frequently asked SQL Query interview questions (solution)
  • How to join three tables in one single SQL query (solution)
  • 5 Courses to learn Database and SQL Better (courses)
  • Top 5 Websites to learn SQL online for FREE? (resource)
  • 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
  • Top 5 Courses to learn MySQL Database for Beginners (Courses)
  • 4 ways to find the Nth highest salary in SQL (solution)
  • Write a SQL query to find all table names on a database in MySQL (solution)
  • 5 Free Courses to learn Oracle and SQL Server? (courses)
  • What is the difference between View and Materialized View in Database? (answer)
  • Difference between clustered and non-clustered index in SQL? (answer)
  • Difference between Primary and Candidate key in table? (answer)
  • 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
  • 5 Advanced SQL books to level up your SQL skills (books)
  • Write a SQL query to copy or backup a table in MySQL (solution)
  • Difference between Unique and Primary key in table? (answer)
  • Top 5 Courses to learn PostgreSQL in-depth (courses)
  • How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
  • Difference between Primary and Foreign key in table? (answer)
  • 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
  • What is the difference between UNION and UNION ALL in SQL? (answer)
  • 4 Free Books to learn Microsoft SQL Server database (books)
  • Top 5 Websites to learn SQL online for FREE? (websites)
  • Top 5 Courses to learn Microsoft SQL Server in-depth (courses)
  • How do you find the duplicate rows in a table on a database? (solution)
  • The real difference between WHERE and HAVING clause in SQL? (answer)
  • 5 Free Courses to learn Database and SQL (free courses)

Thanks for reading this article, if you like this SQL article, then please share with your friends and colleagues. If you have any questions or feedback, then please drop a note.

P.S. - If you are interested in learning Database and SQL and looking for some free resources to start your journey, then you can also take a look at the Introduction to Databases and SQL Querying free course on Udemy to kick-start your learning.