SQL Joins Visual Reference

Sample Tables

users (Table A)

id	name	dept_id
1	Abhishek	10
2	Rahul	20
3	Priya	30
4	Vikram	NULL

departments (Table B)

id	name
10	Engineering
20	Design
40	Marketing

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

INNER JOIN

only matching rows

LEFT JOIN

all of A + matches

RIGHT JOIN

all of B + matches

FULL OUTER JOIN

everything from both

LEFT ONLY

A with no match in 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.name	d.name
Abhishek	Engineering
Rahul	Design

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.name	d.name
Abhishek	Engineering
Rahul	Design
Priya	NULL
Vikram	NULL

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.name	d.name
Abhishek	Engineering
Rahul	Design
NULL	Marketing

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.name	d.name
Abhishek	Engineering
Rahul	Design
Priya	NULL
Vikram	NULL
NULL	Marketing

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

employee	manager
Abhishek	Rahul
Priya	Rahul
Rahul	NULL (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."