What Are SQL JOINs?
In relational databases, data is often spread across multiple tables. SQL JOINs allow you to combine rows from two or more tables based on related columns, enabling comprehensive analysis and reporting.
🎯 Learning Objectives: SQL JOIN
By the end of this tutorial, you will:
✅ Understand INNER, LEFT, RIGHT, and FULL OUTER JOIN
✅ Apply JOINs to real-world scenarios
✅ Use JOINs with filters, aliases, and multiple conditions
✅ Visualize how data is combined across tables
🧠 Base Scenario (Employee – Department Relationship)
We’ll use two real-world inspired tables:
📋 Table 1: Employees
| EmpID | Name | DeptID | Salary |
| 1 | Alice | 101 | 60000 |
| 2 | Bob | 102 | 50000 |
| 3 | Charlie | NULL | 40000 |
| 4 | David | 104 | 55000 |
| 5 | Eva | 103 | 65000 |
📋 Table 2: Departments
| DeptID | DeptName | Location |
| 101 | HR | New York |
| 102 | IT | San Diego |
| 103 | Finance | Chicago |
| 106 | Marketing | Boston |
✅ Note: DeptIDs 104 (used by David) and 106 (no employee) are unmatched across tables.
1️⃣ INNER JOIN: SQL JOIN
🔍 Definition
Returns only the rows where there is a match in both tables.
🧠 Analogy
You’re preparing a report of employees with assigned departments. If the department doesn’t exist, they are excluded.
💻 SQL Query
SELECT E.Name, D.DeptName, D.Location
FROM Employees E
INNER JOIN Departments D ON E.DeptID = D.DeptID;
✅ Output
| Name | DeptName | Location |
| Alice | HR | New York |
| Bob | IT | San Diego |
| Eva | Finance | Chicago |
❌ David (invalid DeptID = 104) and Charlie (no DeptID) are excluded.
📊 Visual Metaphor
[Employees] ∩ [Departments]2️⃣ LEFT JOIN (LEFT OUTER JOIN): SQL JOIN
🔍 Definition
Returns all records from the left table (Employees) and matching records from the right table (Departments). Unmatched departments → NULL.
🧠 Analogy
You want a list of all employees, even if their department is not known or doesn’t exist.
💻 SQL Query
SELECT E.Name, D.DeptName, D.Location
FROM Employees E
LEFT JOIN Departments D ON E.DeptID = D.DeptID;
✅ Output
| Name | DeptName | Location |
| Alice | HR | New York |
| Bob | IT | San Diego |
| Charlie | NULL | NULL |
| David | NULL | NULL |
| Eva | Finance | Chicago |
✅ All employees are listed, even those with missing or invalid DeptIDs.
📊 Visual Metaphor
[Employees] ⟕ [Departments]Read also: How to Learn C++: A Beginner’s Guide for 2025, Best way To Learn C++
3️⃣ RIGHT JOIN (RIGHT OUTER JOIN): SQL JOIN
🔍 Definition
Returns all records from the right table (Departments) and the matching rows from the left table (Employees). Unmatched employees → NULL.
🧠 Analogy
You want a list of all departments, even if no employee belongs to them.
💻 SQL Query
SELECT E.Name, D.DeptName, D.Location
FROM Employees E
RIGHT JOIN Departments D ON E.DeptID = D.DeptID;
✅ Output
| Name | DeptName | Location |
| Alice | HR | New York |
| Bob | IT | San Diego |
| Eva | Finance | Chicago |
| NULL | Marketing | Boston |
✅ All departments included. Marketing has no employees → NULL in Name.
📊 Visual Metaphor
[Employees] ⟖ [Departments]4️⃣ FULL JOIN (FULL OUTER JOIN): SQL JOIN
🔍 Definition
Returns all records from both tables. Matched where possible, and NULL for non-matches.
🧠 Analogy
You want a complete picture — all employees and all departments, whether related or not.
💻 SQL Query
SELECT E.Name, D.DeptName, D.Location
FROM Employees E
FULL OUTER JOIN Departments D ON E.DeptID = D.DeptID;
✅ Output
| Name | DeptName | Location |
| Alice | HR | New York |
| Bob | IT | San Diego |
| Eva | Finance | Chicago |
| Charlie | NULL | NULL |
| David | NULL | NULL |
| NULL | Marketing | Boston |
📊 Visual Metaphor
[Employees] ∪ [Departments] (on DeptID)🧪 Advanced Examples
🎯 1. Find employees without a department
SELECT Name
FROM Employees
LEFT JOIN Departments ON Employees.DeptID = Departments.DeptID
WHERE Departments.DeptID IS NULL;
✅ Result:
| Name |
| Charlie |
| David |
🎯 2. Find departments with no employees
SELECT DeptName
FROM Departments
LEFT JOIN Employees ON Departments.DeptID = Employees.DeptID
WHERE Employees.EmpID IS NULL;
✅ Result:
| DeptName |
| Marketing |
🎯 3. Join with additional conditions (e.g., high earners in Finance)
SELECT E.Name, D.DeptName, E.Salary
FROM Employees E
INNER JOIN Departments D ON E.DeptID = D.DeptID
WHERE D.DeptName = ‘Finance’ AND E.Salary > 60000;
✅ Result:
| Name | DeptName | Salary |
| Eva | Finance | 65000 |
🔁 JOIN Comparison Summary
| JOIN Type | Left Table Included | Right Table Included | NULLs Possible |
| INNER JOIN | ✅ if match | ✅ if match | ❌ |
| LEFT JOIN | ✅ always | ✅ if match | ✅ Right table |
| RIGHT JOIN | ✅ if match | ✅ always | ✅ Left table |
| FULL OUTER | ✅ always | ✅ always | ✅ Both sides |
📦 Practice: Table Creation & Sample Data
CREATE TABLE Employees (
EmpID INT,
Name VARCHAR(50),
DeptID INT,
Salary INT
);
INSERT INTO Employees VALUES
(1, ‘Alice’, 101, 60000),
(2, ‘Bob’, 102, 50000),
(3, ‘Charlie’, NULL, 40000),
(4, ‘David’, 104, 55000),
(5, ‘Eva’, 103, 65000);
CREATE TABLE Departments (
DeptID INT,
DeptName VARCHAR(50),
Location VARCHAR(50)
);
INSERT INTO Departments VALUES
(101, ‘HR’, ‘New York’),
(102, ‘IT’, ‘San Diego’),
(103, ‘Finance’, ‘Chicago’),
(106, ‘Marketing’, ‘Boston’);
🎓 Final Thoughts
Understanding JOINs is fundamental to mastering SQL. Here’s a quick recap:
- INNER JOIN: Only matching records — strict.
- LEFT JOIN: All from the left, fill blanks from the right.
- RIGHT JOIN: All from the right, fill blanks from the left.
- FULL OUTER JOIN: Show everything — match where possible.
👉 Mastering JOINs allows you to build powerful queries, extract hidden insights, and unlock the true potential of your data.
