Multi-Table and Self-Joins
Learn how multi-tables and self-joins work.
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 manager ...