SQL JOINs Explained with Visual Diagrams

Real-world databases split data across multiple tables to avoid repetition. A customer's name doesn't get stored in every order row — it's stored once in a customers table and referenced by a customer_id in the orders table.

This is called normalization — and it's good database design. But it means that when you want to see an order alongside the customer's name, you need to join the two tables together.

Example Tables

customers table:

| customer_id | name | city | |-------------|---------------|-----------| | 4521 | Riya Sharma | Bangalore | | 7832 | Arjun Mehta | Mumbai | | 2219 | Priya Nair | Chennai |

orders table:

| order_id | customer_id | restaurant | amount | |----------|-------------|-------------|--------| | 1001 | 4521 | Burger King | 450 | | 1002 | 7832 | Pizza Hut | 820 | | 1003 | 9999 | Domino's | 310 |

Notice that order_id 1003 has customer_id 9999 — which doesn't exist in the customers table. This matters when you choose your JOIN type.

An INNER JOIN returns only rows where the join condition matches in both tables. If a row in the left table has no match in the right table (or vice versa), it's excluded.

SELECT
  o.order_id,
  c.name AS customer_name,
  c.city,
  o.restaurant,
  o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

Result: Orders 1001 and 1002 appear. Order 1003 is excluded because customer_id 9999 doesn't exist in customers.

Table Aliases

Notice o and c — these are aliases for the table names. They make queries shorter and essential when two tables have columns with the same name:

FROM orders o         -- "o" is now an alias for orders
INNER JOIN customers c -- "c" is now an alias for customers
ON o.customer_id = c.customer_id  -- the join condition

A LEFT JOIN returns all rows from the left table, plus matching rows from the right table. Where there's no match on the right side, the right-side columns return NULL.

SELECT
  o.order_id,
  o.amount,
  c.name AS customer_name,
  c.city
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

Result: All 3 orders appear. Order 1003 still appears, but customer_name and city are NULL because there's no matching customer.

Using LEFT JOIN to Find Missing Matches

This is one of the most powerful patterns in SQL — finding rows in one table that have no corresponding row in another:

-- Find all orders with no matching customer record (orphaned orders)
SELECT o.order_id, o.customer_id, o.amount
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

RIGHT JOIN

RIGHT JOIN is the mirror of LEFT JOIN — it keeps all rows from the right table and matches from the left. In practice, most analysts rewrite RIGHT JOINs as LEFT JOINs by swapping the table order — it's easier to read.

-- These two are equivalent:
SELECT * FROM orders o RIGHT JOIN customers c ON o.customer_id = c.customer_id;
SELECT * FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id;

FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables. Where there's no match on either side, NULLs fill the gaps. Use it when you need a complete picture of both datasets regardless of matches.

SELECT
  o.order_id,
  c.customer_id,
  c.name,
  o.amount
FROM orders o
FULL OUTER JOIN customers c ON o.customer_id = c.customer_id;

Note: MySQL doesn't support FULL OUTER JOIN directly — simulate it with UNION ALL of a LEFT JOIN and RIGHT JOIN.

Real queries often join 3, 4, or more tables. Each JOIN adds one more table to the result:

SELECT
  o.order_id,
  c.name AS customer_name,
  r.restaurant_name,
  r.cuisine_type,
  d.driver_name,
  o.amount,
  o.delivery_time_minutes
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN restaurants r ON o.restaurant_id = r.restaurant_id
LEFT JOIN delivery_drivers d ON o.driver_id = d.driver_id
WHERE o.order_date >= '2026-01-01'
ORDER BY o.delivery_time_minutes DESC;

The LEFT JOIN on delivery_drivers ensures that orders without an assigned driver (maybe delivery is pending) still appear in the results.

Common JOIN Mistakes

1. Forgetting the ON condition — results in a cartesian product (every row × every row)
2. Joining on the wrong column — produces wrong matches silently
3. Ambiguous column names — always prefix with table alias when multiple tables share column names