A join brings related rows together when useful information is split across tables. Instead of storing customer, address, order, product, and item details redundantly, a query follows their relationships and returns the combined view an application needs. section 10.3.4
A practical join follows a small set of decisions:
- Start with a table and give it an alias, such as customer c, to keep a multi-table query readable. section 10.3.4
- Use JOIN to connect tables through a primary key and a related foreign key. section 10.3.4
- Put the matching rule after ON; rows qualify when the key values satisfy that condition. section 10.3.4
- Add GROUP BY when the result should aggregate identical values, then ORDER BY when presentation order matters. section 10.3.4
From orders to invoice data
A simple customer-address join can count how often each address was used. A larger query joins order, customer, address, item, and product, returning each order line with the customer, delivery address, product name, and quantity. This reconstructs the detailed data needed for invoices without requiring every table to repeat the same information. section 10.3.4
SELECT c.name, a.street, a.zipcode, COUNT(c.name) AS 'Times Used' FROM CUSTOMER c JOIN 'order' o ON o.customer_id = c.customer_id JOIN address a ON a.address_id = o.address_id GROUP BY a.street ORDER BY c.name; name street zipcode Times Used ---------- ---------- ---------- ---------- Joe 12 Main St 12345 2 Mary 127 Margol 40322 1 Mary 41 Orange 40321 1 Sue 212 Grove 34213 1
Inspect a complete customer-address join with aliases, key conditions, grouping, sorting, and returned counts. section 10.3.4
