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

By

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 ... ON names the second table and the columns that must match, such as ON 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 Customers to Orders leaves 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's ProductId, ProductName, and SupplierId, a foreign key.
  • Suppliers: each supplier's SupplierId, the primary key, its CompanyName and its Country.

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, ProductName and CompanyName, in that order.
  • Match each product's SupplierId to the supplier's SupplierId.
  • Keep only the rows whose supplier's Country is '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.

Key terms

Cheat sheets