JOINs Explained: INNER, LEFT, RIGHT in Plain English

You now have tables properly connected through foreign keys. JOIN is how you actually pull connected data back together in one query, instead of making separate queries and stitching results together yourself.

Let's use our familiar tables:

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Inner Join Basics

An INNER JOIN (often just written JOIN) returns only rows where a match exists on both sides:

SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Read this left to right: "start with customers, join to orders where customers.id matches orders.customer_id." If Tony Stark has three orders, he appears three times, once per matching order row. If Iron Man has zero orders, he doesn't appear at all, because INNER JOIN only keeps matched pairs.

Left Join Keeps Everything

That last part surprises beginners constantly: "why did my customer disappear from the results?" The answer is almost always that INNER JOIN silently dropped any customer with zero matching orders. If you want every customer to show up regardless of whether they have orders, use LEFT JOIN instead:

SELECT customers.name, orders.total
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

LEFT JOIN keeps every row from the left table (customers) no matter what, and fills in NULL for order columns when there's no match. So Iron Man would appear with total shown as NULL. This is exactly the pattern for questions like "which customers have never placed an order," combined with a WHERE orders.id IS NULL afterward.

RIGHT JOIN is the mirror image, keeping every row from the right table instead. In practice, almost nobody uses RIGHT JOIN, because you can always rewrite it as a LEFT JOIN by swapping which table you list first, and keeping to one consistent pattern (always LEFT JOIN, never RIGHT) makes queries far easier to read across a whole codebase.

Avoiding Accidental Cross Joins

A very common beginner mistake is forgetting the ON condition entirely, or joining on the wrong columns, which produces a cross join: every row from the first table paired with every row from the second, an explosion of mostly meaningless combinations. If a 100-row customers table joins to a 100-row orders table incorrectly, you can accidentally get 10,000 rows back instead of the handful you expected. Always double check your ON condition matches an actual foreign key relationship.

Joining Multiple Tables

You can join more than two tables in one query, chaining joins together:

SELECT customers.name, orders.total, order_items.quantity, products.name AS product_name
FROM orders
JOIN customers ON orders.customer_id = customers.id
JOIN order_items ON order_items.order_id = orders.id
JOIN products ON products.id = order_items.product_id;

Notice AS product_name, an alias. Both orders and products likely have a column called name or similar, and aliasing avoids ambiguity and makes your result columns self-explanatory.

A practical tip: when a query involves several tables, qualify every column with its table name (or a short alias like o for orders, c for customers) even when it's not strictly required. It makes the query far easier to read for the next person, and avoids a confusing "ambiguous column" error if two joined tables happen to share a column name.

Try It Yourself

Worth trying right now: using customers, orders, and order_items from earlier lessons, write a query that lists every customer alongside their order totals using LEFT JOIN, then adjust it to show only customers who have placed zero orders.