Joins

A relational database stores related data across multiple tables. Joins are how you bring that data back together in a query. Instead of one massive table with everything crammed in, you have clean separate tables — customers, orders, products — and joins let you combine them on the fly to answer any question that spans more than one table. Joins are the most important concept in relational SQL, and this lesson covers all four join types with real Dataplexa Store examples.

Why Joins Exist

Consider the Dataplexa Store schema. Customer information lives in customers. Order information lives in orders. The orders table stores a customer_id that references the customer — but it does not store the customer's name or email directly. When you need to show an order alongside the customer's name, you need a join. Joins match rows from two tables based on a condition — usually a foreign key matching a primary key — and return a combined result set.

-- Without a join — orders show customer_id numbers, not names
SELECT id, customer_id, total_amount, status FROM orders LIMIT 3;

-- With a join — orders show the actual customer name
SELECT o.id, c.first_name, c.last_name, o.total_amount, o.status
FROM orders o
JOIN customers c ON o.customer_id = c.id
LIMIT 3;

Table Aliases — Keeping Joins Readable

When joining tables you will frequently reference both table names together in the ON clause and column list. Table aliases give each table a short nickname — o for orders, c for customers, p for products — keeping your queries concise and readable. The alias is defined right after the table name and used everywhere else in the query.

-- Table aliases declared after the table name (AS is optional)
SELECT o.id        AS order_id,
       c.first_name,
       c.last_name,
       c.email,
       o.total_amount,
       o.order_date
FROM orders AS o                        -- alias: o
JOIN customers AS c ON o.customer_id = c.id  -- alias: c
ORDER BY o.order_date DESC
LIMIT 5;

INNER JOIN — Only Matching Rows

An INNER JOIN (or just JOIN) returns only the rows where the join condition is true in both tables. If a customer has no orders, they do not appear. If an order has a customer_id that does not exist in customers, it does not appear either. Both sides must match.

-- INNER JOIN: only customers who have at least one order
SELECT c.first_name, c.last_name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY order_count DESC
LIMIT 5;

-- Join orders to order_items to see line details
SELECT o.id AS order_id,
       o.order_date,
       oi.product_id,
       oi.quantity,
       oi.unit_price
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.id = 101;

LEFT JOIN — All Left Rows, Matching Right Rows

A LEFT JOIN returns every row from the left table, and the matching rows from the right table. Where there is no match on the right side, the right table columns come back as NULL. This is the join to use when you want to include all records from one table regardless of whether they have related records in another — all customers, including those who have never ordered.

-- LEFT JOIN: ALL customers, even those with no orders
-- Customers with no orders will show NULL for order columns
SELECT c.first_name, c.last_name, c.email,
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name, c.email
ORDER BY total_spent DESC;

-- Find customers who have NEVER ordered — NULL on the right side
SELECT c.first_name, c.last_name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL
ORDER BY c.last_name;

COALESCE(value, fallback) replaces NULL with a fallback value — here it turns NULL spending into 0.00 for cleaner output.

RIGHT JOIN — All Right Rows, Matching Left Rows

A RIGHT JOIN is the mirror of LEFT JOIN — it returns every row from the right table, with matching rows from the left. In practice, RIGHT JOINs are rarely used because you can always rewrite them as a LEFT JOIN by swapping the table order, which most developers find more readable.

-- RIGHT JOIN: all orders, matching customers
-- Functionally equivalent to the LEFT JOIN version with tables swapped
SELECT c.first_name, c.last_name, o.id AS order_id, o.total_amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id
ORDER BY o.id
LIMIT 5;

-- The same result using LEFT JOIN (preferred style)
SELECT c.first_name, c.last_name, o.id AS order_id, o.total_amount
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
ORDER BY o.id
LIMIT 5;

FULL JOIN — All Rows from Both Tables

A FULL JOIN (also called FULL OUTER JOIN) returns every row from both tables. Where there is a match, the columns from both sides are populated. Where there is no match on either side, the non-matching side's columns come back as NULL. It is less common than INNER and LEFT joins but useful for comparing two datasets and finding what is in one but not the other.

-- FULL JOIN: see all customers and all orders
-- Unmatched customers show NULL order columns
-- Unmatched orders show NULL customer columns
SELECT c.first_name, c.last_name,
       o.id AS order_id, o.total_amount
FROM customers c
FULL JOIN orders o ON c.id = o.customer_id
WHERE c.id IS NULL OR o.id IS NULL   -- show only the unmatched rows
ORDER BY c.last_name;

Joining Three or More Tables

You can chain as many joins as you need in a single query. Each JOIN adds another table and its own ON condition. This is how you get a full order receipt — customer name from customers, order details from orders, product name from products, all in one result.

-- Full order receipt: customer + order + line items + product names
SELECT c.first_name || ' ' || c.last_name AS customer,
       o.id         AS order_id,
       o.order_date,
       p.name       AS product,
       oi.quantity,
       oi.unit_price,
       oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN customers c    ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p     ON oi.product_id = p.id
WHERE o.id = 101
ORDER BY p.name;

Join Types Visual Summary

Next up: Advanced join techniques — self joins, CROSS JOIN, and joining on multiple conditions.