Join keys and relationship cardinality

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Match orders to customers and reason about one-to-many relationships.

Run the example

SELECT TOP (5) o.OrderID, o.CustomerID, c.FirstName
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerID = o.CustomerID
ORDER BY o.OrderID;

Read it step by step

1. Orders.CustomerID is a foreign key to the unique Customers.CustomerID.
2. Each order matches one customer; one customer can match many orders.
3. The join adds the customer name, and ORDER BY selects a repeatable five-order sample.

Check the result

The result grain remains one row per selected order because CustomerID is unique on the customer side.

Practice

Show the two order-item lines for OrderID 1, including each line’s ProductID.

SELECT o.OrderID, i.LineNumber, i.ProductID
FROM dbo.Orders AS o
JOIN dbo.OrderItems AS i ON i.OrderID = o.OrderID
WHERE o.OrderID = 1
ORDER BY i.LineNumber;