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

Hello guys, are you looking for a simple example of how to use SELF JOIN in SQL? If yes, then you have come to the right place. This article will show you how to use Self join in solving interesting SQL problems from LeetCode. Along the way, you will also learn this useful SQL concept. So, what are you waiting for? Let's first check the problem, and then we'll write an SQL query using SELF Join to solve this problem.

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 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 the Person and their Address
4. How to find the Nth highest salary 

These SQL problems are a 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 that every programmer and data scientist learn SQL basics and practice SQL problems like this one to get their mind working as a SQL engine. 

Once you pass through all these basic SQL query questions, you can try your hand on the most complex ones given in SQL 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 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 like The 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's, we first need to find employees who are a manager, then we compare their salaries to find out all employees who earn more than their managers.

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.

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

In these example, I have used MySQL database but SQL are written in ANSI SQL style so they will work in all database like Oracle, SQL Server Management Studio as well as PostgreSQL

# 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 employees who are managers :

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

That's all about the Self Join example in the Database. You know it is a great way to learn essential SQL concepts by using the Leetcode database problem of finding employees earning more than their managers. LeetCode also has a good collection of SQL problems that 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 indexes in SQL? (answer)
• What difference between the Primary and Candidate key in the 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 the Unique and Primary keys in the table? (answer)
• Top 5 Courses to learn PostgreSQL in-depth (courses)
• How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
• What difference between the Primary and Foreign key in the 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 it 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.