The sample dataset
We use two tables throughout this guide. customers(id, name, country) holds 4 rows, and orders(id, customer_id, amount, created_at) holds 6 rows. One customer has never ordered, and one order references a customer_id that no longer exists. These two edge cases are exactly what makes each JOIN type behave differently, so keep them in mind as you read.
INNER JOIN: only matching rows
An INNER JOIN returns rows where the join condition is true on both sides. The customer without orders disappears, and so does the orphan order. Use it when you only care about records that exist in both tables, for example revenue per active customer.
SELECT c.name, SUM(o.amount) AS revenue
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY revenue DESC;LEFT JOIN: keep everything on the left
A LEFT JOIN keeps every customer, even those without orders; their order columns are NULL. This is the right choice for questions like "which customers never ordered?". Add WHERE o.id IS NULL to isolate them. A common bug is filtering the right table in the WHERE clause (WHERE o.amount > 100), which silently turns the LEFT JOIN back into an INNER JOIN. Put that condition in the ON clause instead.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;RIGHT and FULL OUTER JOIN
RIGHT JOIN is a mirrored LEFT JOIN; most teams rewrite it as a LEFT JOIN with the tables swapped for readability. FULL OUTER JOIN keeps unmatched rows from both sides, which makes it ideal for reconciliation: comparing an export from your CRM against your billing system and listing differences. MySQL does not support FULL OUTER JOIN natively; emulate it with a LEFT JOIN UNION a RIGHT JOIN.
The duplicate-rows trap
If you join customers to orders and then to payments, each customer row is multiplied by orders × payments. Summing amounts after such a join inflates totals. The fix is to aggregate each child table in a subquery or CTE first, then join the aggregated results on the customer id.
WITH o AS (
SELECT customer_id, SUM(amount) AS ordered
FROM orders GROUP BY customer_id
), p AS (
SELECT customer_id, SUM(amount) AS paid
FROM payments GROUP BY customer_id
)
SELECT c.name, o.ordered, p.paid
FROM customers c
LEFT JOIN o ON o.customer_id = c.id
LEFT JOIN p ON p.customer_id = c.id;Checklist before running a JOIN in production
1) Count rows before and after the join to detect duplication. 2) Make sure join columns are indexed. 3) Prefer explicit column lists over SELECT *. 4) Decide consciously how NULLs should be treated (COALESCE(o.amount, 0) for totals). When you ask FormulAI for a query, describe the tables and the expected result in one sentence; the generated SQL comes with an explanation you can check against this list.