Joining tables with INNER JOIN

Combine rows from two tables by matching an ID, and know what one row of the result means.

opportunities stores an account_id, not the account’s name. To show deals with their account name, join the two tables:

SELECT o.opportunity_id,
       o.amount,
       a.name AS account_name
FROM opportunities AS o
INNER JOIN accounts AS a
        ON a.account_id = o.account_id;

o and a are table aliases (lesson 7). ON says which rows belong together.

How a join works, step by step

Take two small tables:

accounts

opportunities

For each pair of rows where ON is true, the join builds one new row. The result:

Three things to notice:

• Acme appears twice, once per matching deal. The result has one row per matching pair, not one row per account.
• Cedar is gone. It has no deals, so nothing matched.
• Deal 104 is gone. It points to account 9, which doesn’t exist.

INNER JOIN keeps only rows with a match on both sides. A plain JOIN means the same thing.

Choosing join keys

The ON condition usually matches an ID in one table to the main ID of another:

ON o.account_id = a.account_id   -- each deal points to one account
ON o.owner_id   = u.user_id      -- the names can differ, that's fine

• Match on IDs, not names. 'Acme Inc' and 'ACME, Inc.' won’t match, and those rows silently vanish.
• NULL keys never match. A deal with no owner (owner_id is NULL) drops out of an inner join to users.

Joining a third table

Each JOIN attaches another table to the result built so far:

SELECT o.name     AS deal_name,
       a.name     AS account_name,
       u.name     AS rep_name
FROM opportunities AS o
JOIN accounts AS a ON a.account_id = o.account_id
JOIN users    AS u ON u.user_id    = o.owner_id;

Two tables here have a name column, so the prefix and an alias for each are required.

Always check the row count

Before you trust a join, compare the count with what you expected:

SELECT COUNT(*)                         AS rows_after_join,
       COUNT(DISTINCT o.opportunity_id) AS unique_deals
FROM opportunities AS o
JOIN accounts AS a ON a.account_id = o.account_id;

If the two numbers differ, rows multiplied, and any SUM(amount) on top would be inflated. If the count is lower than the number of deals, rows went missing.

Try it

List every won deal with its account. Return the deal name, the account name, the account’s industry and the deal amount, largest first.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson