A join combines related rows from separate tables in one query. It lets an order record lead to its customer, its purchased items, and each item’s title instead of storing all of that data in one oversized table. section 8.0 section 8.2.2
Write the first table after FROM, add JOIN for each related table, and use ON to state the matching columns. Qualify shared column names with their table names so SQL knows exactly which value you mean. section 8.2.1 section 8.2.2
Find the customer and item in an order
Start with orderheader, join customer through CustomerID, then join orderitem through OrderID. Add title only after orderitem, because orderitem supplies the TitleID that connects to title. Filter the selected order with WHERE. section 8.2.2
Keep joins readable:
- Use explicit JOIN ... ON syntax rather than putting all table-matching predicates in WHERE; separating relationships from filters is easier to troubleshoot. section 8.4
- Add as many joins as needed, but ensure both tables named in an ON condition have already been declared. section 8.2.2
- Use the same matching relationship when filtering from either joined table; matching OrderID values can produce the same result either way. section 8.6
