SQL JOINs Explained: INNER, LEFT, RIGHT and FULL

The short answer

A JOIN puts rows from two tables side by side when a condition is true, usually when a foreign key equals a primary key. The join type decides what happens to rows that have no match:

Two small tables

customers

orders

Bobur has no orders. Order 104 is a guest order with no customer.

INNER JOIN

SELECT c.name, o.id AS order_id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

Only pairs that match. Bobur and order 104 disappear. Plain JOIN means INNER JOIN.

LEFT JOIN

SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Every customer is present. This is the go-to join for "all X, with Y if it exists" and for finding rows without a pair:

-- customers who never ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

RIGHT JOIN

SELECT c.name, o.id AS order_id, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

Every order is present. A RIGHT JOIN is a LEFT JOIN with the tables swapped, so many teams write only LEFT JOINs for readability.

FULL JOIN

SELECT c.name, o.id AS order_id, o.total
FROM customers c
FULL JOIN orders o ON o.customer_id = c.id;

Everything from both sides. Useful for comparing two lists, for example reconciling payments with invoices. MySQL does not support FULL JOIN; combine a LEFT and a RIGHT JOIN with UNION instead.

Duplicate rows: why sums grow

Aziz appears twice above because he has two orders. That is correct, but it becomes a trap when you join two one-to-many tables at once:

-- wrong: orders x payments multiply each other
SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o   ON o.customer_id = c.id
JOIN payments p ON p.customer_id = c.id
GROUP BY c.name;

If Aziz has 2 orders and 3 payments, each order row is repeated 3 times and the sum is inflated. Fix it by aggregating each table separately first:

SELECT c.name, o.orders_total, p.paid_total
FROM customers c
LEFT JOIN (SELECT customer_id, SUM(total)  AS orders_total FROM orders   GROUP BY customer_id) o ON o.customer_id = c.id
LEFT JOIN (SELECT customer_id, SUM(amount) AS paid_total   FROM payments GROUP BY customer_id) p ON p.customer_id = c.id;

If you see DISTINCT added just to hide duplicates, check the join conditions first.

Self join

A table joined to itself, with two aliases. Classic case: employees and their managers in one table.

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

LEFT JOIN keeps the top manager, whose manager_id is NULL.

Joining three or more tables

Joins are applied one after another, each adding a table to the result:

SELECT o.id, c.name, p.title, oi.qty
FROM orders o
JOIN customers   c  ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id = oi.product_id;

Tips: give every table a short alias, write each ON condition next to its table, and index the foreign key columns used in joins.

Common mistakes

• Filtering the right table in WHERE after a LEFT JOIN: WHERE o.total > 10 removes the NULL rows and silently turns it into an INNER JOIN. Put the condition into ON instead.
• Forgetting the ON condition or using the wrong column, which produces a huge cross product.
• Comparing with NULL using =: NULL = NULL is not true, so rows with NULL keys never match.