SQL JOINs Explained: Visual Guide to INNER, LEFT, RIGHT, FULL

SQL JOINs are the most important concept for combining data from multiple tables. This guide explains every JOIN type with clear examples you can practice immediately.

What is a JOIN?

A JOIN combines rows from two or more tables based on a related column. Without JOINs, you would need to run multiple queries and combine results manually in application code.

INNER JOIN

Returns only rows that have matching values in both tables. This is the most common JOIN type.

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Result: Only users who have placed orders appear. Users without orders are excluded.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, and matched rows from the right table. Unmatched right rows show NULL.

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Result: All users appear, even those without orders. Their order columns show NULL.

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table, and matched rows from the left table. Unmatched left rows show NULL.

SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

Result: All orders appear, even those with no matching user (orphaned records).

FULL OUTER JOIN

Returns all rows from both tables. Unmatched rows from either side show NULL. Not supported in MySQL — use UNION of LEFT and RIGHT JOIN instead.

SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

CROSS JOIN

Returns the Cartesian product — every row from the first table combined with every row from the second. Use with caution: 100 rows x 100 rows = 10,000 results.

SELECT u.name, p.product_name
FROM users u
CROSS JOIN products p;

Self JOIN

A table joined with itself. Useful for hierarchical data like employee-manager relationships.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Quick Reference

INNER JOIN — only matching rows
LEFT JOIN — all from left + matches from right
RIGHT JOIN — all from right + matches from left
FULL OUTER JOIN — all from both tables
CROSS JOIN — every combination
SELF JOIN — table joined with itself

Practice Your Queries

Use the SQL Formatter to beautify your JOIN queries for better readability. Clean SQL makes it easier to debug complex multi-table joins.