INNER JOIN
INNER JOIN combines two tables on a matching column, keeping only rows that match. Table aliases keep it short, and the joined columns can be filtered, grouped and sorted like any others.
Journeyman · T-SQL · SQL · Updated
After this lesson you can
- Join two tables with INNER JOIN ... ON, using table aliases
- Filter, group and sort joined rows, using columns from both tables
Key takeaways
INNER JOIN ... ONnames the second table and the columns that must match, such asON p.CategoryId = c.CategoryId.- When a column name is in both tables, put the table name or alias in front of it, such as
p.CategoryId. - Give each table a short alias with
AS, and once it has one, use the alias everywhere in that query. - The joined result works like one table: filter, group and sort it using columns from both tables.
- Rows with no match disappear: joining
CustomerstoOrdersleaves out the two customers who never ordered.
INNER JOIN combines two tables, keeping only the rows that match. You name the second table, and say which columns must match with ON:
SELECT TOP (4) Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN Categories
ON Products.CategoryId = Categories.CategoryId
ORDER BY Products.ProductId;
| ProductName | CategoryName |
|---|---|
| Chai | Beverages |
| Chang | Beverages |
| Aniseed Syrup | Condiments |
| Chef Anton's Cajun Seasoning | Condiments |
Every product has a category, so without the TOP, all 77 products come back, each with its category's name.
Naming columns: table.column
Both tables have a CategoryId column, so ON CategoryId = CategoryId would be meaningless. Write the table's name first: Products.CategoryId. When a column name exists in both tables, you must say which you mean, anywhere in the query; when it's in only one, the table name is optional, but clearer.
Table aliases
Typing Products. and Categories. everywhere gets long. Give each table a short alias with AS, and use it instead:
SELECT TOP (4) p.ProductName, c.CategoryName, p.UnitPrice
FROM Products AS p
INNER JOIN Categories AS c
ON p.CategoryId = c.CategoryId
WHERE c.CategoryName = 'Seafood'
ORDER BY p.UnitPrice DESC;
| ProductName | CategoryName | UnitPrice |
|---|---|---|
| Carnarvon Tigers | Seafood | 62.50 |
| Ikura | Seafood | 31.00 |
| Gravad lax | Seafood | 26.00 |
| Nord-Ost Matjeshering | Seafood | 25.89 |
Once a table has an alias, use the alias everywhere in that query. (INNER JOIN can also be written as just JOIN; this course writes INNER to be clear.)
Joins with everything else
The joined result works like one big table: filter it with WHERE (as above, by the category's name), sort it with ORDER BY, and group it. Products per category, now with names instead of ids:
SELECT c.CategoryName, COUNT(*) AS Products, AVG(p.UnitPrice) AS AveragePrice
FROM Products AS p
INNER JOIN Categories AS c
ON p.CategoryId = c.CategoryId
GROUP BY c.CategoryName
ORDER BY Products DESC, c.CategoryName;
| CategoryName | Products | AveragePrice |
|---|---|---|
| Confections | 13 | 25.16 |
| Beverages | 12 | 37.9791 |
| Condiments | 12 | 23.0625 |
| Seafood | 12 | 20.6825 |
| Dairy Products | 10 | 28.73 |
| Grains/Cereals | 7 | 20.25 |
| Meat/Poultry | 6 | 54.0066 |
| Produce | 5 | 32.37 |
Rows without a match disappear
An inner join keeps only matches. Join Customers to Orders and count:
SELECT COUNT(*) AS Rows
FROM Customers AS c
INNER JOIN Orders AS o
ON c.CustomerId = o.CustomerId;
| Rows |
|---|
| 830 |
One row per order, 830 in all. The two customers with no orders aren't there: there was nothing to pair them with. Often that's what you want. When it isn't, you need an outer join.
Do it: list the products from suppliers in Japan
A buyer is planning a trip to Japan and wants a list of the products that come from suppliers there, each beside its supplier's name.
Two tables hold what the list needs:
Products: each product'sProductId,ProductName, andSupplierId, a foreign key.Suppliers: each supplier'sSupplierId, the primary key, itsCompanyNameand itsCountry.
They hold these rows (the Tables tab shows the SQL that makes them):
| SupplierId | CompanyName | Country |
|---|---|---|
| 1 | Exotic Liquids | UK |
| 4 | Tokyo Traders | Japan |
| 6 | Mayumi's | Japan |
| 7 | Pavlova, Ltd. | Australia |
| ProductId | ProductName | SupplierId |
|---|---|---|
| 1 | Chai | 1 |
| 2 | Chang | 1 |
| 3 | Aniseed Syrup | 1 |
| 9 | Mishi Kobe Niku | 4 |
| 10 | Ikura | 4 |
| 13 | Konbu | 6 |
| 14 | Tofu | 6 |
| 15 | Genen Shouyu | 6 |
| 16 | Pavlova | 7 |
| 74 | Longlife Tofu | 4 |
For example, Ikura's SupplierId is 4. Supplier 4 is Tokyo Traders, in Japan, so Ikura is on the list, beside Tokyo Traders. Chai's supplier is 1, Exotic Liquids, in the UK, so Chai isn't.
Your query should give exactly these rows, in this order:
| ProductName | CompanyName |
|---|---|
| Genen Shouyu | Mayumi's |
| Ikura | Tokyo Traders |
| Konbu | Mayumi's |
| Longlife Tofu | Tokyo Traders |
| Mishi Kobe Niku | Tokyo Traders |
| Tofu | Mayumi's |
Your job: write one SELECT query that joins Products to Suppliers with INNER JOIN.
- Show two columns,
ProductNameandCompanyName, in that order. - Match each product's
SupplierIdto the supplier'sSupplierId. - Keep only the rows whose supplier's
Countryis'Japan'. - Sort the rows by
ProductName, A to Z.
Both tables have a SupplierId column, so say which table you mean each time you use it: Products.SupplierId, or p.SupplierId once Products has the alias p.
Practiced live on the lesson page: write the SQL right there and check it.
Check yourself
2 questions to try on the lesson page.
So what
INNER JOIN ... ON puts related rows side by side, and from then on it's a table like any other: filter it, group it, sort it. Most reports you'll ever write start with one.