Preface
Self-Join: this term is used when you want to compare data within a single table, so you will create copies of the table to make the comparison via some sort of JOIN
e.g. INNER JOIN, LEFT JOIN, etc
This article provides a basic overview of this concept, but I'll simplify the example so that it is more practical based on a Leetcode question.
Tips
Full term and its syntactic sugar equivalent:
INNER JOIN == JOIN
LEFT OUTER JOIN == LEFT JOIN
RIGHT OUTER JOIN == RIGHT JOIN
FULL OUTER JOIN == FULL JOIN
For some varied practice on SQL Queries check out the leetcode database exercises. Filter by 'easy' at first work through those to practice your SQL!
Introduction
In this article, we're going to break down a leetcode database question. The challenge is based on a LeetCode exercise titled "181. Employees Earning More Than Their Managers" See here.
The below showcases a theoretical way of thinking through this exercise.
The Challenge
Here's the schema for the Employee table:
CREATE TABLE IF NOT EXISTS Employee (id int, name varchar(255), salary varchar(255), managerId int);
TRUNCATE table Employee;
INSERT INTO Employee (id, name, salary, managerId) values (1, 'Joe', 70000, 3), (2, 'Henry', 80000, 4) , (3, 'Sam', '60000', NULL), (4, 'Max', '90000',NULL);
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
+-------------+---------+
Each row indicates an employee's ID, name, salary, and their manager's ID. The goal is to write a SQL query to find employees who earn more than their managers.
Sample Data
Consider the following employee data:
| id | name | salary | managerId |
| -- | ----- | ------ | --------- |
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | null |
| 4 | Max | 90000 | null |
Understanding SQL Joins
To solve this challenge, we need to understand how SQL joins work, especially when a table is joined with itself. Let's examine.
Visualizing the Join
A helpful approach is to initially think of the query as SELECT * to visualize the entire joined table before focusing on the specific columns to select. This approach allows us to see the complete picture of how rows are paired during the join.
SELECT *
FROM Employee AS e
JOIN Employee AS m
ON e.managerId = m.id;
On the above, we gave the first copy of the table an alias 'e' for 'employee' and the second copy of the table an alias 'm' for 'manager'
When we execute this query, we're essentially pairing each employee with their manager.
Keep in mind line ON e.managerId = m.id;
These values need to be equivalent in our joined table.
The resulting joined table looks like this:
| e.id | e.name | e.salary | e.managerId | m.id | m.name | m.salary | m.managerId |
|------|--------|----------|-------------|------|--------|----------|-------------|
| 1 | Joe | 70000 | 3 | 3 | Sam | 60000 | null |
| 2 | Henry | 80000 | 4 | 4 | Max | 90000 | null |
Piecing together the Logic
Now that we have our joined table, we must return employees who have a higher salary than their managers.
This means adding this statement to the end of the query:
WHERE e.salary > m.salary;
Given our current SQL query:
-- SELECT statement to be inserted here...
FROM Employee AS e
JOIN Employee AS m
ON e.managerId = m.id
WHERE e.salary > m.salary;
We can now implement the SELECT statement to show the employees from the the "employee" side of our joined table - those who make more than their manager.
Solution Query
SELECT e.name AS Employee
FROM Employee AS e
JOIN Employee AS m
ON e.managerId = m.id
WHERE e.salary > m.salary;
OUTPUT:
| Employee |
| -------- |
| Joe |
This query compares each employee's salary with their manager's salary and selects the employees who earn more.
Conclusion
Understanding SQL joins, especially self-joins, can be confusing at first so I hope this method of reasoning through it can give you some help in making sense of these as you deepen your SQL knowledge.
Top comments (1)
Thanks for this very useful post. I just wanted to add that, there is a very easy way now, to test all the SQLs described here, using the free & portable tools, mentioned in my latest post here : dev.to/linuxguist/learn-sql-quickl...