SQL Joins Visualized
Understand SQL joins with clear examples. Learn INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN with practical use cases.
Setup: Two Simple Tables
All examples use these tables:
-- users table
| id | name |
|----|--------|
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
-- orders table
| id | user_id | product |
|----|---------|----------|
| 1 | 1 | Laptop |
| 2 | 1 | Mouse |
| 3 | 2 | Keyboard |
| 4 | 99 | Monitor |INNER JOIN
Returns only rows that have a match in both tables. This is the most common join type.
SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id;
-- Result:
-- Alice | Laptop
-- Alice | Mouse
-- Bob | Keyboard
-- (Carol has no orders, order #4 has no matching user → both excluded)LEFT JOIN
Returns all rows from the left table, with NULLs where there is no match in the right table. Use this when you want all records from the primary table regardless of whether related data exists.
SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- Result:
-- Alice | Laptop
-- Alice | Mouse
-- Bob | Keyboard
-- Carol | NULL ← Carol appears even with no ordersRIGHT JOIN and FULL JOIN
RIGHT JOIN is the mirror of LEFT JOIN — it keeps all rows from the right table.
FULL OUTER JOIN keeps all rows from both tables, filling NULLs on either side where there is no match.
-- FULL OUTER JOIN
SELECT users.name, orders.product
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;
-- Result:
-- Alice | Laptop
-- Alice | Mouse
-- Bob | Keyboard
-- Carol | NULL ← from left table
-- NULL | Monitor ← from right table (user_id 99 doesn't exist)When to Use Each Join
- INNER JOIN — you only want matched data (users who have orders)
- LEFT JOIN — you want all items from the primary table, even without matches (all users, even those without orders)
- RIGHT JOIN — rarely used; rewrite as LEFT JOIN with reversed table order
- FULL JOIN — you need to find mismatches on both sides (orphaned records, data reconciliation)
- CROSS JOIN — cartesian product of both tables; useful for generating combinations (sizes × colors)
Related Tutorials
REST API Design Basics
Learn how to design clean, consistent REST APIs. Covers resource naming, HTTP methods, status codes, pagination, and versioning.
Docker Fundamentals
Get started with Docker containers. Learn images, containers, volumes, networking, and how to write a Dockerfile from scratch.