🔗 The 4 Types of SQL Joins
Master INNER, LEFT, RIGHT, and FULL OUTER joins with visual examples
INNER JOIN
Returns only matching rows from both tables
Table A
Table B
Table A (employees)
id
name
1
Alice
2
Bob
3
Charlie
Table B (departments)
id
dept
1
Sales
2
Engineering
4
Marketing
Result
id
name
dept
1
Alice
Sales
2
Bob
Engineering
SELECT
e.id,
e.name,
d.dept
FROM employees e
INNER JOIN departments d
ON e.id = d.id;
LEFT JOIN
Returns all rows from left table + matching rows from right
Table A
Table B
Table A (employees)
id
name
1
Alice
2
Bob
3
Charlie
Table B (departments)
id
dept
1
Sales
2
Engineering
4
Marketing
Result
id
name
dept
1
Alice
Sales
2
Bob
Engineering
3
Charlie
NULL
SELECT
e.id,
e.name,
d.dept
FROM employees e
LEFT JOIN departments d
ON e.id = d.id;
RIGHT JOIN
Returns all rows from right table + matching rows from left
Table A
Table B
Table A (employees)
id
name
1
Alice
2
Bob
3
Charlie
Table B (departments)
id
dept
1
Sales
2
Engineering
4
Marketing
Result
id
name
dept
1
Alice
Sales
2
Bob
Engineering
4
NULL
Marketing
SELECT
e.id,
e.name,
d.dept
FROM employees e
RIGHT JOIN departments d
ON e.id = d.id;
FULL OUTER JOIN
Returns all rows from both tables (with NULLs where no match)
Table A
Table B
Table A (employees)
id
name
1
Alice
2
Bob
3
Charlie
Table B (departments)
id
dept
1
Sales
2
Engineering
4
Marketing
Result
id
name
dept
1
Alice
Sales
2
Bob
Engineering
3
Charlie
NULL
4
NULL
Marketing
SELECT
e.id,
e.name,
d.dept
FROM employees e
FULL OUTER JOIN departments d
ON e.id = d.id;
📋 Quick Reference Guide
When to Use INNER JOIN
- You only want matching records
- Both tables must have corresponding values
- Example: Find customers who made purchases
When to Use LEFT JOIN
- You want all records from the first table
- Even if there's no match in the second table
- Example: All employees, with their departments (if assigned)
When to Use RIGHT JOIN
- You want all records from the second table
- Even if there's no match in the first table
- Example: All departments, with their employees (if any)
When to Use FULL OUTER JOIN
- You want all records from both tables
- Show all relationships, even if incomplete
- Example: Complete picture of employees and departments