INNER JOIN: matching rows across tables

A join puts rows from two tables side by side. INNER JOIN returns one row for every pair of rows, one from each table, for which the ON condition is true. Rows that find no partner are left out.

1SELECT o.OrderId, o.OrderDate, c.FirstName, c.LastName2FROM dbo.Orders AS o3INNER JOIN dbo.Customers AS c4    ON c.CustomerId = o.CustomerId5ORDER BY o.OrderId;

OrderId  OrderDate   FirstName  LastName
-------  ----------  ---------  --------
      1  2024-02-03  Asha       Patel
      2  2024-02-10  Ben        Carter
      3  2024-03-01  Asha       Patel
      4  2024-03-15  Dev        Singh
      5  2024-04-02  Farid      Khan
      6  2024-04-20  Chloe      Evans
      7  2024-05-05  Dev        Singh
      8  2024-05-18  Zoë        Müller
      9  2024-06-01  Ben        Carter
     10  2024-06-12  Farid      Khan

• o and c are table aliases. They are short names for the tables in this query, and o.OrderDate means “OrderDate from Orders”.
• ON says which rows belong together, almost always a foreign key = the primary key it points at.
• INNER is optional: a plain JOIN is an inner join.

1SELECT c.CustomerId, c.FirstName, o.OrderId, o.CustomerId2FROM dbo.Customers AS c3INNER JOIN dbo.Orders AS o4    ON o.CustomerId = c.CustomerId;

A customer with two orders appears twice, once paired with each order. A customer with no orders (Ella, Grace) does not appear at all. That is what “inner” means: only matched rows. The next lesson keeps the unmatched ones.

Ambiguous column names

Both tables have a CustomerId column. If you name it without saying which table, SQL Server refuses:

1SELECT OrderId, CustomerId, FirstName2FROM dbo.Orders AS o3JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId;

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'CustomerId'.

Joining more than two tables

Each extra JOIN brings in one more table, with its own ON condition linking it to a table already in the query. This follows the path order → customer, order → lines → products, and works out each line’s total:

1SELECT o.OrderId, c.FirstName, p.Name AS Product,2       oi.Quantity, oi.UnitPrice,3       oi.Quantity * oi.UnitPrice AS LineTotal4FROM dbo.Orders AS o5JOIN dbo.Customers  AS c  ON c.CustomerId = o.CustomerId6JOIN dbo.OrderItems AS oi ON oi.OrderId   = o.OrderId7JOIN dbo.Products   AS p  ON p.ProductId  = oi.ProductId8WHERE o.OrderId <= 39ORDER BY o.OrderId, p.Name;

OrderId  FirstName  Product            Quantity  UnitPrice  LineTotal
-------  ---------  -----------------  --------  ---------  ---------
      1  Asha       Claw hammer               1      18.50      18.50
      1  Asha       Tape measure 8 m          2       9.75      19.50
      2  Ben        Cement 25 kg             10       7.20      72.00
      2  Ben        Sand 25 kg               10       4.10      41.00
      3  Asha       Cordless drill            1      89.00      89.00
      3  Asha       Wood screws (200)         2      11.90      23.80

The order of the tables in the FROM clause does not change the result of inner joins. Start wherever reads naturally. Here the question is about products, so the query starts from the order lines and fetches the rest:

1SELECT c.FirstName, c.City, p.Name AS Product, o.OrderDate2FROM dbo.OrderItems AS oi3JOIN dbo.Products   AS p   ON p.ProductId    = oi.ProductId4JOIN dbo.Categories AS cat ON cat.CategoryId = p.CategoryId5JOIN dbo.Orders     AS o   ON o.OrderId      = oi.OrderId6JOIN dbo.Customers  AS c   ON c.CustomerId   = o.CustomerId7WHERE cat.Name = N'Tools'8ORDER BY o.OrderDate;

FirstName  City       Product           OrderDate
---------  ---------  ----------------  ----------
Asha       Leeds      Claw hammer       2024-02-03
Asha       Leeds      Tape measure 8 m  2024-02-03
Asha       Leeds      Cordless drill    2024-03-01
Zoë        Harrogate  Claw hammer       2024-05-18
Farid      York       Tape measure 8 m  2024-06-12

ON can test more than keys

The ON condition can be any true-or-false test. This one pairs each order line with its product only when the price paid was lower than the list price, which finds discounts:

1SELECT oi.OrderId, p.Name, p.Price AS ListPrice, oi.UnitPrice AS PricePaid2FROM dbo.OrderItems AS oi3JOIN dbo.Products AS p4    ON p.ProductId = oi.ProductId5   AND oi.UnitPrice < p.Price;

OrderId  Name          ListPrice  PricePaid
-------  ------------  ---------  ---------
      7  Cement 25 kg       7.20       7.00

The old comma syntax

Older code lists the tables with commas and puts the join condition in WHERE. It gives the same result:

1SELECT o.OrderId, c.FirstName2FROM dbo.Orders AS o, dbo.Customers AS c3WHERE c.CustomerId = o.CustomerId4  AND o.Status = 'Pending';

OrderId  FirstName
-------  ---------
      8  Zoë
      9  Ben

Prefer JOIN … ON. It keeps each join condition next to its table, separate from the filters, and if you forget a condition you get a syntax error. With commas, a forgotten condition silently pairs every row with every row (a CROSS JOIN, two lessons on).

1SELECT o.OrderId, c.FirstName, p.Name AS Product, oi.Quantity2FROM dbo.Orders AS o3JOIN dbo.Customers  AS c  ON c.CustomerId = o.CustomerId4JOIN dbo.OrderItems AS oi ON oi.OrderId   = o.OrderId5JOIN dbo.Products   AS p  ON p.ProductId  = oi.ProductId6WHERE o.Status = 'Pending'7ORDER BY o.OrderId, p.Name;

OrderId  FirstName  Product              Quantity
-------  ---------  -------------------  --------
      8  Zoë        Claw hammer                 1
      8  Zoë        Plywood sheet 18 mm         2
      9  Ben        Wall plugs (100)            4