Self JOIN in MySQL - Mini Project: Build & Apply

Understanding Self JOIN in MySQL

📖 Scenario: You work in a company database where employees have managers who are also employees. You want to find out who manages whom by linking employees to their managers.

🎯 Goal: Build a MySQL query using SELF JOIN to list employees alongside their managers' names.

📋 What You'll Learn

Create a table called employees with columns id, name, and manager_id

Insert the exact employee data with their manager IDs

Write a SELECT query using SELF JOIN to get employee names and their managers' names

Use table aliases e for employees and m for managers in the query

💡 Why This Matters

🌍 Real World

Companies often store employee and manager data in the same table. Self joins help find relationships within the same dataset.

💼 Career

Understanding self joins is essential for database roles that involve hierarchical data, reporting structures, or organizational charts.

1

Create the employees table

Write a MySQL statement to create a table called employees with columns: id as INT primary key, name as VARCHAR(50), and manager_id as INT that can be NULL.

MySQL

-- Create the employees table with id, name, and manager_id columns
-- Your code here

Need a hint?

Use CREATE TABLE with the specified columns and types. manager_id can be NULL because some employees may not have managers.

2

Insert employee data

Insert these exact rows into the employees table: (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2). Use a single INSERT INTO statement.

MySQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT NULL
);
-- Insert the employee data below
-- Your code here

Need a hint?

Use a single INSERT INTO statement with multiple rows separated by commas.

3

Write the SELF JOIN query

Write a SELECT query using SELF JOIN on the employees table with aliases e for employees and m for managers. Select e.name as employee and m.name as manager. Join on e.manager_id = m.id.

MySQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT NULL
);

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2);

-- Write the SELF JOIN query below
-- Your code here

Need a hint?

Use JOIN on the same table with aliases and match e.manager_id to m.id.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by employee name in ascending order.

MySQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT NULL
);

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2);

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
-- Add ORDER BY clause below
-- Your code here

Need a hint?

Use ORDER BY e.name ASC to sort employees alphabetically.