Learnixo
Back to blog
Backend Systemsbeginner

SQL JOIN: Combining Tables

Master SQL JOINs: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and SELF JOIN. Learn when to use each with clear diagrams and real-world examples.

Asma HafeezApril 17, 20265 min read
sqljoinsdatabaserelationships
Share:š•

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 |

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

Enjoyed this article?

Explore the Backend Systems learning path for more.

Subscribe for weekly tutorials

Found this helpful?

Share:š•

Leave a comment

Have a question, correction, or just found this helpful? Leave a note below.