← back
Mirage / Database / SQL Joins Visual Reference
Database
Database
SQL Joins Visual Reference
Every join type with Venn diagrams, real sample data, actual result tables, and when to use each one.
Sample Tables
users (Table A)
idnamedept_id
1Abhishek10
2Rahul20
3Priya30
4VikramNULL
departments (Table B)
idname
10Engineering
20Design
40Marketing
Note: Priya (dept 30) has no matching department. Vikram has NULL dept_id. Marketing (40) has no users. These gaps show the difference between join types.
Visual Overview
A B
INNER JOIN
only matching rows
A B
LEFT JOIN
all of A + matches
A B
RIGHT JOIN
all of B + matches
A B
FULL OUTER JOIN
everything from both
A B
LEFT ONLY
A with no match in B
A B
CROSS JOIN
every row × every row
Each Join Explained
INNER JOIN
most common
returns only rows where the condition matches in BOTH tables. rows with no match are excluded.
SELECT u.name, d.name
FROM users u
INNER JOIN departments d
  ON u.dept_id = d.id
result: (Priya and Vikram excluded — no match)
u.named.name
AbhishekEngineering
RahulDesign
LEFT JOIN
2nd most common
returns ALL rows from the left table, plus matching rows from right. non-matching right side = NULL.
SELECT u.name, d.name
FROM users u
LEFT JOIN departments d
  ON u.dept_id = d.id
result: all users shown, NULL where no dept
u.named.name
AbhishekEngineering
RahulDesign
PriyaNULL
VikramNULL
RIGHT JOIN
rare — use LEFT instead
returns ALL rows from right table, plus matching rows from left. same as LEFT JOIN with tables swapped.
SELECT u.name, d.name
FROM users u
RIGHT JOIN departments d
  ON u.dept_id = d.id
result: all depts shown, NULL where no user
u.named.name
AbhishekEngineering
RahulDesign
NULLMarketing
FULL OUTER JOIN
all rows
returns ALL rows from both tables. NULLs fill in wherever there's no match on either side.
SELECT u.name, d.name
FROM users u
FULL OUTER JOIN departments d
  ON u.dept_id = d.id
result: everything, NULLs where unmatched
u.named.name
AbhishekEngineering
RahulDesign
PriyaNULL
VikramNULL
NULLMarketing
LEFT ONLY (Anti-join)
find missing
find rows in left table that have NO match in right table. useful for finding orphaned records.
SELECT u.name
FROM users u
LEFT JOIN departments d
  ON u.dept_id = d.id
WHERE d.id IS NULL
result: users with no department
u.name
Priya
Vikram
SELF JOIN
table joins itself
join a table to itself. classic use case: employee → manager relationship in the same table.
-- find employee + their manager
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.id
same table, aliased twice
employeemanager
AbhishekRahul
PriyaRahul
RahulNULL (is CEO)
When to Use What
Use INNER JOIN when
you only want rows where both sides have matching data. most queries. "get users with their orders."
Use LEFT JOIN when
you want all rows from one table even if the other has nothing. "get all users even if they have no goals."
Use LEFT + WHERE NULL when
you want to find missing relationships. "find users who have never placed an order."