Line Items per Order with Lateral — LATERAL Joins in SQL

The problem

Brightlane's fulfillment operations team is conducting a line-item audit and needs every product included in every order.

Write a query to return one row per order line item, showing the order ID, customer ID, product ID, and unit price for that item.

Assumptions:

An order with multiple line items contributes one output row per line item.
Orders with no line items on record do not appear in the result.

Output:

One row per order line item, with columns order_id, customer_id, product_id, 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
  o.id AS order_id,
  o.customer_id,
  items.product_id,
  items.unit_price
FROM
  orders o
  CROSS JOIN LATERAL (
    SELECT
      product_id,
      unit_price
    FROM
      order_items oi
    WHERE
      oi.order_id = o.id
  ) items

The shape

The lateral subquery sees o.id from the outer order row and pulls back every line item linked to that order. Because the lateral returns one row per line item, the outer order row is duplicated once per item, which is exactly the per-line-item shape the audit asks for.

Clause by clause

SELECT o.id AS order_id, o.customer_id, items.product_id, items.unit_price returns the order's id and customer from the outer table, and the line item's product and unit price from the lateral.
FROM orders o is the driving table; the lateral will be evaluated once for each order row.
CROSS JOIN LATERAL ( SELECT product_id, unit_price FROM order_items oi WHERE oi.order_id = o.id ) items runs once per order and returns every matching line item. An order with three items contributes three output rows; an order with no items returns zero rows from the lateral. CROSS JOIN then drops that outer order, which matches the prompt's rule that orders with no line items do not appear.

Why this and not a plain inner join

FROM orders o INNER JOIN order_items oi ON oi.order_id = o.id returns the same rows on this data, and on a flat per-line-item audit either form is correct. The lateral version generalises better. The moment the inner query needs an aggregate, an ORDER BY ... LIMIT per order, or a GROUP BY over each order's items, the inner join cannot express it without a derived table; the lateral already has the shape that supports any of those extensions. Reading this query as "for each order, get its line items" mirrors the audit's question more directly than a flat join.

You practiced CROSS JOIN LATERAL returning multiple rows per outer record — each parent record pairs with as many lateral rows as the inner query produces; parents with zero matches are dropped.

Return one row per order line item, showing the order ID, customer ID, product ID, and unit price for that item

The shape

Clause by clause

Why this and not a plain inner join

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