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

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