LEFT, RIGHT, and FULL JOINS
Learn how to use LEFT, RIGHT, and FULL JOINS to manage missing data.
Step 1: Start with a question
In the last lesson, you combined two tables, employees and departments, using an INNER JOIN. But what if something’s missing?
Imagine this:
Some employees don’t yet belong to a department.
Some departments currently have no employees.
Employee table:
ID | first_name | last_name | department_id | Salary |
1 | Raj | Patel | 1 | 95000.00 |
2 | David | Nguyen | 1 | 102000.00 |
3 | Maria | Lopez | 4 | 88000.00 |
4 | Sofia | Rossi | 5 | 72000.00 |
5 | James | Olsen | 3 | 68000.00 |
6 | Omar | Hassan | 3 | 85000.00 |
7 | Lina | Chen | 6 | 64000.00 |
8 | Noah | Khan | 2 | 65000.00 |
9 | Alice | Kim | 2 | 75000.00 |
10 | Isabella | Garcia | 1 | 97000.00 |
11 | Ethan | Brown | NULL | 60000.00 |
12 | Zara | Khan | NULL | 55000.00 |
Department table:
department_id | department_name | manager_id |
1 | Engineering | 2 |
2 | Marketing | 1 |
3 | Sales | 3 |
4 | Finance | 4 |
5 | Design | 5 |
6 | Customer Support | 6 |
7 | R&D | 7 |
If you use INNER JOIN, those records disappear. What if you want to keep them visible?
Let’s ask AI for help.
Write a SQL query that shows all employees and their department names, even if some employees don’t belong to a department.
Step 2: Run and observe
AI will likely generate:
Run it. Now look closely ...