SQL JOINs Explained: Combine Data From Multiple Tables

Databases split information into separate tables: customers in one, orders in another. That keeps each fact in one place, but most questions need data from several tables at once. Which customer placed this order? Which products have never sold? A JOIN combines rows from two tables into one result, based on a matching column.

Why data lives in separate tables#

Imagine storing everything in one big table:

If Asha moves to Pune, every one of her orders must be updated, and missing one leaves the data contradicting itself. So instead, customer details are stored once in customers, and each order stores only the customer's id. JOINs reunite the pieces at query time. (See SQL Database Basics for primary and foreign keys.)

The example tables#

customers

orders

Two details make these tables a good test of every JOIN type: Meera and John have no orders, and order 104 belongs to customer 5, who does not exist.

How a JOIN works#

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

• FROM customers c JOIN orders o names the two tables (with short aliases).
• ON o.customer_id = c.customer_id is the join condition: it says which rows belong together.

For each pair of rows where the condition is true, the database produces one combined row. The JOIN type decides what happens to rows that have no partner.

The JOIN types at a glance#

INNER JOIN: only matches#

name | order_id | amount
-----+----------+-------
Asha | 101      | 250
Asha | 102      | 120
Ravi | 103      | 560

LEFT JOIN: all customers#

name  | order_id | amount
------+----------+-------
Asha  | 101      | 250
Asha  | 102      | 120
Ravi  | 103      | 560
Meera | NULL     | NULL
John  | NULL     | NULL

FULL OUTER JOIN: everything#

name  | order_id | amount
------+----------+-------
Asha  | 101      | 250
Asha  | 102      | 120
Ravi  | 103      | 560
Meera | NULL     | NULL
John  | NULL     | NULL
NULL  | 104      | 90

NULL fills the columns of the side that had no match.

Choosing the right JOIN#

Ask one question: which rows must not disappear?

• Only complete pairs matter → INNER JOIN.
• Every row of the first table must appear → LEFT JOIN.
• Every row of both tables must appear → FULL OUTER JOIN.
• You need every combination (rare) → CROSS JOIN.

In practice, INNER JOIN and LEFT JOIN cover the vast majority of real queries. RIGHT JOIN is almost always rewritten as a LEFT JOIN with the tables swapped.

JOIN syntax rules#

• JOIN on its own means INNER JOIN; LEFT JOIN means LEFT OUTER JOIN.
• Prefix columns with their table alias (c.name, o.amount), especially columns that exist in both tables.
• The ON condition usually connects a foreign key to the primary key it refers to.
• You can join more than two tables by adding more JOIN ... ON ... clauses (see Multiple JOINs).

Common mistakes#

Practice#

Key takeaway#

Start with the most common type: INNER JOIN.