SQL JOIN: Combining Tables

Joins combine rows from two or more tables based on related columns. They're the most important SQL concept after SELECT.

Sample Tables

SQL

-- customers
CREATE TABLE customers (
    id   INT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50)
);

-- orders
CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT,
    amount      DECIMAL(10, 2),
    status      VARCHAR(20)
);

-- products
CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  VARCHAR(100),
    price DECIMAL(10, 2)
);

-- order_items
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT
);

INNER JOIN — Only Matching Rows

Returns rows where the join condition is true in both tables.

SQL

-- Get orders with customer names
SELECT
    o.id          AS order_id,
    c.name        AS customer,
    o.amount,
    o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

order_id | customer | amount  | status
---------|----------|---------|----------
1        | Alice    | 250.00  | shipped
2        | Bob      | 99.00   | pending
3        | Alice    | 500.00  | delivered

Customers with no orders are excluded. Orders with no matching customer are excluded.

LEFT JOIN — All Left Rows

Returns all rows from the left table, and matching rows from the right. Unmatched right rows become NULL.

SQL

-- All customers, with orders if they have any
SELECT
    c.name,
    o.id     AS order_id,
    o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
ORDER BY c.name;

name  | order_id | amount
------|----------|--------
Alice | 1        | 250.00
Alice | 3        | 500.00
Bob   | 2        | 99.00
Carol | NULL     | NULL      -- Carol has no orders

Find customers who have never ordered

SQL

SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;  -- NULL means no match

RIGHT JOIN — All Right Rows

All rows from the right table, matching rows from the left. Less common — usually rewritten as a LEFT JOIN by swapping table order.

SQL

-- All orders, with customer names if available
SELECT o.id, c.name
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

-- Equivalent (preferred):
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

FULL OUTER JOIN — All Rows from Both

Returns all rows from both tables. NULLs where there's no match on either side.

SQL

SELECT
    c.name     AS customer,
    o.amount   AS order_amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

customer | order_amount
---------|-------------
Alice    | 250.00
Alice    | 500.00
Bob      | 99.00
Carol    | NULL          -- customer with no orders
NULL     | 175.00        -- orphan order with no customer

Multiple JOINs

SQL

-- Get order details: customer, order, products
SELECT
    c.name        AS customer,
    o.id          AS order_id,
    p.name        AS product,
    oi.quantity,
    p.price,
    (oi.quantity * p.price) AS line_total
FROM orders o
JOIN customers c    ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON oi.product_id = p.id
ORDER BY o.id, p.name;

SELF JOIN — A Table Joining Itself

Used for hierarchical or comparison relationships within one table.

SQL

-- employees table with manager_id referencing same table
CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       VARCHAR(50),
    manager_id INT  -- references employees.id
);

-- Get each employee with their manager's name
SELECT
    e.name            AS employee,
    m.name            AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

CROSS JOIN — Every Combination

Every row from the left paired with every row from the right. Use rarely — the result grows fast.

SQL

-- 4 sizes × 3 colors = 12 combinations
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

JOIN Performance Tips

SQL

-- Always join on indexed columns
-- orders.customer_id should have an index:
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Don't SELECT * in joins — name the columns you need
-- Bad:
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

-- Good:
SELECT o.id, o.amount, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;

Common Patterns

Aggregating with JOIN

SQL

-- Total order value per customer
SELECT
    c.name,
    COUNT(o.id)    AS order_count,
    SUM(o.amount)  AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC NULLS LAST;

Filtering on Joined Table

SQL

-- Customers in Oslo who placed orders over 200
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.city = 'Oslo'
  AND o.amount > 200;

Key Takeaways

| JOIN Type | Returns | |-----------|---------| | INNER JOIN | Rows matching in both tables | | LEFT JOIN | All left rows + matching right (NULL if no match) | | RIGHT JOIN | All right rows + matching left (NULL if no match) | | FULL OUTER JOIN | All rows from both, NULLs where unmatched | | CROSS JOIN | Cartesian product — every combination | | SELF JOIN | Same table joined to itself |

INNER JOIN is the default and most common
LEFT JOIN + WHERE right.id IS NULL finds records with no match — very useful for finding orphans
Always join on indexed foreign key columns for performance
Alias your tables (FROM orders o) to write shorter, readable queries