SQL - Self-Join Intro with PostgreSQL: The Inner Join
Tags: sql, postgres, programming, database
Published: 2023-12-16 21:43:02


## 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](https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-self-join/) 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](https://en.wikipedia.org/wiki/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](https://leetcode.com/problemset/database/?difficulty=EASY&page=1). 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](https://leetcode.com/problems/employees-earning-more-than-their-managers/). 

The below showcases a theoretical way of thinking through this exercise.

### The Challenge

Here's the schema for the `Employee` table:
```sql
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);
```

```markdown
+-------------+---------+
| 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:

```markdown
| 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.

```sql
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:

```markdown
| 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:
```sql
WHERE e.salary > m.salary;
```

Given our current SQL query:
```sql
-- 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

```sql
SELECT e.name AS Employee
FROM Employee AS e
JOIN Employee AS m
ON e.managerId = m.id
WHERE e.salary > m.salary;
```

OUTPUT:
```sql
| 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.