SQL JOINs Reference Guide
Complete reference for all SQL JOIN types with syntax and examples.
INNER JOIN
Syntax:
SELECT columns FROM table1 INNER JOIN table2 ON table1.col = table2.col
Returns only rows with matching values in both tables.
Example:
SELECT o.id, c.name FROM orders o INNER JOIN customers c ON o.customer_id = c.id;
Set Diagram: A ∩ B
LEFT JOIN
Syntax:
SELECT columns FROM table1 LEFT JOIN table2 ON table1.col = table2.col
Returns all rows from left table plus matching rows from right table. NULL for non-matches.
Example:
SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;
Set Diagram: A ∪ (A ∩ B)
RIGHT JOIN
Syntax:
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.col = table2.col
Returns all rows from right table plus matching rows from left table.
Example:
SELECT o.id, c.name FROM orders o RIGHT JOIN customers c ON o.customer_id = c.id;
Set Diagram: (A ∩ B) ∪ B
FULL OUTER JOIN
Syntax:
SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.col = table2.col
Returns all rows from both tables, with NULLs for non-matches.
Example:
SELECT c.name, o.id FROM customers c FULL OUTER JOIN orders o ON c.id = o.customer_id;
Set Diagram: A ∪ B
CROSS JOIN
Syntax:
SELECT columns FROM table1 CROSS JOIN table2
Returns Cartesian product - every combination of rows from both tables.
Example:
SELECT color, size FROM colors CROSS JOIN sizes;
Set Diagram: A × B
SELF JOIN
Syntax:
SELECT columns FROM table1 t1 JOIN table1 t2 ON t1.col = t2.col
Joins a table with itself. Requires table aliases.
Example:
SELECT e.name, m.name as manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;
Set Diagram: A ⋈ A
Quick Reference Table
| Function | Purpose | |----------|---------| | INNER JOIN | Returns only rows with matching values in both tables | | LEFT JOIN | Returns all rows from left table plus matching rows from right table | | RIGHT JOIN | Returns all rows from right table plus matching rows from left table | | FULL OUTER JOIN | Returns all rows from both tables, with NULLs for non-matches | | CROSS JOIN | Returns Cartesian product - every combination of rows from both tables | | SELF JOIN | Joins a table with itself |
Related Cheat Sheets
Continue learning with more SQL references: