Relationships and JOINs
INNER JOIN and correct matching
Combine data through its relationships.
Understand the concept
INNER JOIN returns matching row pairs. ON specifies the relationship keys. A customer with three orders appears in three result rows; this is the expected consequence of a one-to-many relationship, not necessarily a duplicate-data error.
Aliases clarify identically named columns. Identify the relationship and result grain first. If you need one row per customer, aggregate the orders rather than expecting JOIN to do it automatically.
Matching column types do not prove a relationship: ProductId and CustomerId may both be integers but identify different entities. Check key semantics. Reconcile matched and unmatched record counts separately in your tests.
Use it to show orders with customer details or lines with product names.
Omitting the join condition or adding DISTINCT without understanding multiplicity.
One topic. Three levels of depth.
SELECT o.OrderId, c.FullName FROM lab.SalesOrder AS o
INNER JOIN lab.Customer AS c ON c.CustomerId = o.CustomerId ORDER BY o.OrderId;Returns 12 order rows; each order has a valid customer.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Return the customer and product names for order 101.
Show the solution and explanation
SELECT c.FullName, p.ProductName FROM lab.SalesOrder AS o JOIN lab.Customer AS c ON c.CustomerId = o.CustomerId JOIN lab.OrderItem AS i ON i.OrderId = o.OrderId JOIN lab.Product AS p ON p.ProductId = i.ProductId WHERE o.OrderId = 101;Ada appears twice because the order contains two product lines.