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
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.CategoryIdto the primary key inCategories. - An inner join keeps only the rows that match: joining
CustomerstoOrdersleaves out the two customers who never ordered. - A left outer join keeps those rows too, with
NULLin 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'sProductName, and itsCategoryId, the foreign key.Categories: each category'sCategoryId, the primary key, and itsCategoryName.
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,
ProductNameandCategoryName, in that order. - Keep the starter's
FROMandINNER JOINlines 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.