LEFT, RIGHT, and FULL JOINS
Explore how to use LEFT, RIGHT, and FULL JOINS in SQL to combine data across tables while handling missing records. Learn to interpret NULL values and choose the right join type to reveal complete or partial datasets in real-world analysis scenarios.
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 ...