Explore how different SQL JOIN types combine rows from two tables based on a related column.
Table A (Left)
EMPLOYEES
ID
Name
DeptID
Table B (Right)
DEPARTMENTS
ID
DeptName
SELECT * FROM Employees
INNER JOIN Departments
ON Employees.DeptID = Departments.ID;
Result Set
READY
Emp.ID
Name
Emp.DeptID
Dept.ID
DeptName
A Visual Guide to SQL JOINs
Unlock the power of relational data by mastering how to combine tables. This guide and the interactive tool will make it click.
Quick Context
In a relational database, data is split into multiple tables to avoid redundancy (a practice called normalization). SQL JOINs are the mechanism to bring that related data back together for querying. They combine rows from two or more tables based on a related column between them.
The Core Idea: Finding Pairs
At its heart, a JOIN operation is like a matching game. The database engine takes a row from the first table (the "left" table) and compares its "join key" (e.g., `DeptID`) to the join key of every row in the second table (the "right" table). When it finds a match, it combines the two rows into a single, wider row in the result set.
The different types of JOINs (`INNER`, `LEFT`, `RIGHT`, `FULL`) simply change the rules for what happens when a row in one table doesn't have a matching partner in the other.
Guided Experiments with the Visualizer
The Venn diagram is your best friend here. It shows exactly which data will be included. Use it to predict the outcome before running the visualizer.
The Standard: `INNER JOIN`
Select `INNER JOIN` (the default). Notice the Venn diagram only highlights the intersection.
Click Run Visualizer. The animation will only create result rows when an `Employees.DeptID` finds a matching `Departments.ID`.
Observation: Employee 'David' (DeptID 103) and the 'Sales' department (ID 104) are excluded from the result because they don't have a match in the other table.
All from the Left: `LEFT JOIN`
Switch to `LEFT JOIN`. The Venn diagram now includes all of the left circle (Employees).
Run the visualizer. The animation proceeds as before, but when it gets to 'David' and 'Eve', it can't find a match.
Observation: Instead of discarding them, the `LEFT JOIN` keeps the employee rows and fills the department columns with `NULL`. All employees are present in the result.
All from the Right: `RIGHT JOIN`
Switch to `RIGHT JOIN`. Now the right circle (Departments) is fully highlighted.
Run the visualizer. This time, the logic focuses on the departments table.
Observation: The 'Sales' department (ID 104), which has no employees, is now included in the result, with `NULL` values for the employee columns.
Everything: `FULL OUTER JOIN`
Select `FULL OUTER JOIN`. The entire Venn diagram is highlighted.
Run the visualizer.
Observation: The result set now includes everyone and every department. Rows that didn't have a match in the other table are preserved, with `NULL`s filling in the gaps. It's the combination of a `LEFT JOIN` and a `RIGHT JOIN`.
Thinking in Pseudocode
Here's a simplified mental model for how a database might process a `LEFT JOIN`:
result_set = []
for each employee in Employees_Table:
found_a_match = false
for each department in Departments_Table:
if employee.DeptID == department.ID:
combined_row = combine(employee, department)
add combined_row to result_set
found_a_match = true
if not found_a_match:
combined_row = combine(employee, NULL_department) // Keep the left row
add combined_row to result_set
return result_set
Key Takeaways
`INNER JOIN`: Only matching rows. Use this when you only care about data that exists in both tables (e.g., "Show me employees who are in a valid department").
`LEFT JOIN`: All rows from the left table, plus matches from the right. Use this when you want to keep all records from the primary table, even if they don't have a corresponding entry in the other (e.g., "Show me ALL employees and their department name, if they have one").
`RIGHT JOIN`: All rows from the right table. Less common, but useful for finding records in the right table that don't have a match in the left (e.g., "Show me all departments, and which employees are in them").
`FULL OUTER JOIN`: All rows from both tables. Use when you need a complete picture of all data from both tables, regardless of whether it matches.
Cheat sheet
SQL Joins Visualizer
In a relational database, data is split into multiple tables to avoid redundancy (a practice called normalization). SQL JOINs are the mechanism to bring that related data back together for querying. They combine rows from two or more tables based on a related column between them.