Advanced SQL Tutorial Part 4 — SELF JOINS & SQL SET OPERATIONS

SQL SELF-JOINS 🤝 SQL UNION 🙏

Photo by Nijwam Swargiary on Unsplash

🔹 What is a Self-Join?

• A self-join is when a table is joined with itself.
• It’s useful when you want to compare rows within the same table.
• You use table aliases to treat the same table as if it were two different tables

🔹 Syntax

SELECT a.column, b.column
FROM table_name a
JOIN table_name b
  ON <condition>;

🔹 Example 1: Employees & Managers

Suppose we have a table:

| emp_id | emp_name | manager_id |
| ------- | --------- | ----------- |
| 1       | Alice     | 3           |
| 2       | Bob       | 3           |
| 3       | Cathy     | NULL        |
| 4       | David     | 2           |

👉 We want to find each employee’s manager's name.

We want to create a query that returns each employee’s name along with their manager’s name.

Since the manager_id refers to another emp_id in the same table, we can use a self-join.

SELECT e.emp_name AS employee,
       m.emp_name AS manager
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.emp_id;