Order Items with Product Names — INNER JOIN in SQL

The problem

Brightlane's fulfilment team needs a manifest of all order line items, showing the product name, quantity, and unit price for each line.

Write a query to return one row per order line item with the matching product name plus the line's quantity and unit price.

Assumptions:

The order_items table contains one row per product per order; product_id on each line points to a row in products.
The products table contains every product in the catalogue, identified by products.id.
Every line item has a valid product_id.

Output:

One row per order line item, with columns product_name, quantity, and unit_price.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  p.name AS product_name,
  oi.quantity,
  oi.unit_price
FROM
  order_items oi
  JOIN products p ON oi.product_id = p.id

The shape

Each row in order_items represents one product on one order, and the line carries a product_id reference but not the product's name. Joining to products on that reference attaches the catalogue name to every line, so the fulfilment manifest reads as Men's Slim Jeans, 1, 59.99 instead of 42, 1, 59.99.

Clause by clause

FROM order_items oi reads from the line-items table and aliases it as oi. Every row here is one product on one order, with quantity and unit_price recorded at the time of purchase and product_id as the link back to the catalogue.
JOIN products p ON oi.product_id = p.id pairs each line with its product. For every row in order_items, PostgreSQL finds the row in products whose id equals that line's product_id, and emits a combined row holding both sides. Because every line has a valid product_id, every line item appears in the result.
SELECT p.name AS product_name, oi.quantity, oi.unit_price picks the product name from the catalogue side and the per-line quantity and unit price from the line itself. The qualifier on p.name is structural. Both tables carry an id column, and writing oi.product_id = id (without the p.) would be ambiguous. Once the aliases are in place, every column reference reads cleanly: p.name is from products, oi.quantity and oi.unit_price are from the line.

You practiced joining a junction-style fact table (order_items) to one of its parent dimensions (products). The recurring shape: junction tables almost always join to multiple parents; this problem joins to one of them, and joining to the customer/order side later follows the same pattern.

Return one row per order line item with the matching product name plus the line's quantity and unit price

The shape

Clause by clause

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.