SQL Inner Join
An inner join keeps matching pairs and drops rows without a match. It is a natural choice when the report only makes sense for records with a related row, such as order lines that identify a product.
Understand the idea
The order_items table links orders and products. One order can contain several products, so this query returns one row per order line. Unit price belongs to the line because it records the price charged at that time. A later change to a product's current price should not rewrite an old receipt.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT i.order_id, p.name, i.quantity,
i.quantity * i.unit_price_cents AS line_cents
FROM order_items AS i
INNER JOIN products AS p ON p.product_id = i.product_id
ORDER BY i.order_id, i.product_id;
Read the result
Five lines are returned. Order 101 contains two notebooks worth 500 cents and one lamp worth 1800 cents. Its two lines add to the stored order total of 2300 cents.
Try one change
Filter to order 102 and verify the line total using the quantity and recorded unit price.