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.
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.