Joining Tables in T-SQL: an overview

A join puts related rows from two tables side by side, matched on their key columns. An inner join keeps only matches; an outer join also keeps the rows with no match.

Apprentice · T-SQL · SQL · Updated

By

After this lesson you can

  • Explain what a join does, and the difference between an inner and an outer join

Key takeaways

  • A join puts related rows from two tables side by side, matching a foreign key such as Products.CategoryId to the primary key in Categories.
  • An inner join keeps only the rows that match: joining Customers to Orders leaves out the two customers who never ordered.
  • A left outer join keeps those rows too, with NULL in the other table's columns: 832 rows instead of 830.

Northwind keeps each kind of thing in its own table: products in Products, categories in Categories. That avoids repeating a category's name on every product. But a report that shows each product with its category's name needs both tables at once.

A join does that: it puts related rows from two tables side by side, in one result.

Matching on keys

Tables are related through their keys. Each product has a CategoryId (a foreign key) that matches the CategoryId (the primary key) of one row in Categories:

Products.ProductName Products.CategoryId Categories.CategoryId Categories.CategoryName
Chai 1 → 1 Beverages
Chang 1 → 1 Beverages
Aniseed Syrup 2 → 2 Condiments

A join walks the arrows: for each product, it finds the category row with the same CategoryId, and makes one result row from the two.

Two kinds of join

The question is what to do with a row that has no match.

Join Keeps
INNER JOIN Only the rows that match in both tables
OUTER JOIN (LEFT, RIGHT or FULL) The matches, plus the rows from one or both tables that have no match, with NULL where the other table's columns would be

Northwind has two customers who've never placed an order. Join Customers to Orders:

  • An inner join leaves those two customers out: there's no order to pair them with. 830 rows, one per order.
  • A left outer join keeps them, with NULLs in the order columns. 832 rows.

The next lessons show both, and then joins across more than two tables.

Do it: show each product beside its category's name

The product report shows a number where the category should be: 1, 2, 3. The people who read it want the category's name instead.

Two tables hold what the report needs:

  • Products: each product's ProductName, and its CategoryId, the foreign key.
  • Categories: each category's CategoryId, the primary key, and its CategoryName.

They hold these rows (the Tables tab shows the SQL that makes them):

ProductName CategoryId
Chai 1
Chang 1
Aniseed Syrup 2
Chef Anton's Cajun Seasoning 2
Pavlova 3
Teatime Chocolate Biscuits 3
CategoryId CategoryName
1 Beverages
2 Condiments
3 Confections

The starter already joins the two tables. Its INNER JOIN ... ON line walks the arrows: for each product, it finds the category row with the same CategoryId. (The next lesson teaches how to write one.) After the join, the columns of both tables sit side by side, each named table.column, such as Categories.CategoryName.

For example, Chai's CategoryId is 1, and category 1 is Beverages, so Chai's row in the report is Chai | Beverages.

Your query should give exactly these rows, in this order:

ProductName CategoryName
Aniseed Syrup Condiments
Chai Beverages
Chang Beverages
Chef Anton's Cajun Seasoning Condiments
Pavlova Confections
Teatime Chocolate Biscuits Confections

Your job: change the starter's query so the report shows each category's name, not its number.

  • Show two columns, ProductName and CategoryName, in that order.
  • Keep the starter's FROM and INNER JOIN lines as they are.
  • Sort the rows by ProductName, A to Z.

Practiced live on the lesson page: write the SQL right there and check it.

Check yourself

1 question to try on the lesson page.

So what

Joins are how a database that stores each fact once gives you reports that show everything together. Pick inner when you only want matches, outer when the rows without a match matter too.

Key terms

Cheat sheets