Real data rarely lives in one table. Orders sit in one table, customers in another, products in a third. A JOIN is how SQL puts them together. Most beginner mistakes in reports come from choosing the wrong type of join, so it is worth getting this right early.
The example tables
Imagine a customers table with customer_id and name, and an orders table with order_id, customer_id and amount. Some customers have never ordered, and one order has a customer_id that is missing from the customers table (a data quality problem you will meet often).
INNER JOIN
SELECT c.name, o.amount FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id;
Returns only rows that match in both tables. Customers with no orders disappear, and so does the order with the unknown customer. Use it when you only care about matched records.
LEFT JOIN
Keeps every row from the left table (customers) and adds order details where they exist. Customers with no orders appear with NULL in the amount column. This is the join you need for questions like “which customers have never ordered?”: add WHERE o.order_id IS NULL.
RIGHT JOIN
The mirror image: keeps every row from the right table (orders). In practice most analysts rewrite it as a LEFT JOIN by swapping the table order, because it is easier to read.
FULL OUTER JOIN
Keeps all rows from both tables, matched where possible and NULL where not. It is useful for reconciliation work, such as finding records that exist in one system but not the other. Note that MySQL does not support FULL JOIN directly; you combine a LEFT and a RIGHT join with UNION.
Three checks before you trust a join
- Count rows before and after. If the count grows unexpectedly, the join key is not unique and rows are being duplicated.
- Look for NULLs. Unexpected NULLs usually mean missing or mistyped keys.
- Check totals. Sum the amount before and after the join. If revenue changes, something is wrong.
Continue with GROUP BY and HAVING and NULL vs zero in dashboards.
SQL is taught with real datasets in our Data Analytics course in Bangalore.
Book a free demo class at our BTM Layout centre, and ask a counsellor for batch details.
