Multi-Table and Self-Joins
Explore how to use multi-table joins to combine data across related tables and self-joins to link rows within the same table. Learn to write SQL queries that connect employees, departments, and managers while visualizing data relationships to deepen your understanding of complex datasets.
Step 1: Start with a question
So far, you’ve joined two tables, employees and departments. Let’s imagine updated data with these three tables:
Departments →
department_id,department_name,manager_id
department_id | department_name | manager_id | manager_name |
1 | Engineering | 2 | Marcus Chen |
2 | Marketing | 1 | Evelyn Turner |
3 | Sales | 3 | Priya Singh |
4 | Finance | 4 | William Johnson |
5 | Design | 5 | Hannah Lee |
6 | HR | 6 | Carlos Rivera |
7 | Operations | 7 | Emma Davis |
Employees →
employee_id,name,department,department_id,salary,manager_id
id | first_name | last_name | department | department_id | salary |
1 | Alice | Kim | Marketing | 2 | 75000.00 |
2 | Raj | Patel | Engineering | 1 | 95000.00 |
3 | Maria | Lopez | Finance | 4 | 88000.00 |
4 | David | Nguyen | Engineering | 1 | 102000.00 |
5 | Sofia | Rossi | Design | 5 | 72000.00 |
6 | James | Olsen | Sales | 3 | 68000.00 |
7 | Lina | Chen | HR | 6 | 64000.00 |
8 | Omar | Hassan | Operations | 7 | 85000.00 |
9 | Ethan | Brown | Sales | 3 | 62000.00 |
10 | Isabella | Garcia | Engineering | 1 | 97000.00 |
11 | Noah | Khan | Marketing | 2 | 65000.00 |
12 | Grace | Lee | Finance | 4 | 91000.00 |
13 | Hugo | Miller | Engineering | 1 | 89000.00 |
14 | Ava | Singh | Sales | 3 | 67000.00 |
15 | Leo | Martinez | Design | 5 | 74000.00 |
Managers →
manager_id,manager_name
manager_id | manager_name |
1 | Evelyn Turner |
2 | Marcus Chen |
3 | Priya Singh |
4 | William Johnson |
5 | Hannah Lee |
6 | Carlos Rivera |
7 | Emma Davis |
8 | Daniel Kim |
9 | Sophia Roberts |
10 | Liam Patel |
You might ask: Can I see each employee’s name, their department, and their manager’s name, all in one result?
Prompt: Write a SQL query that shows each employee’s first name, employee last name, department name, and ...