Left Lateral Keeping All Customers — LATERAL Joins in SQL

The problem

Brightlane's customer success team is auditing order history across every account. Every customer must appear, including those with no orders.

Write a query to return every customer's ID and name alongside the ID and total amount of each order they have placed. Customers with no orders on record should still appear with missing values in the order columns.

Assumptions:

The customers table has one row per customer with an id and a name.
The orders table has one row per order, linked to a customer by customer_id and carrying a total_amount.
A customer with multiple orders contributes one row per order. A customer with no orders contributes a single row with missing values in the order columns.

Output:

One row per customer-order pairing, plus one row per customer with no orders, with columns id, name, order_id, and total_amount.

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
  c.id,
  c.name,
  all_orders.id AS order_id,
  all_orders.total_amount
FROM
  customers c
  LEFT JOIN LATERAL (
    SELECT
      id,
      total_amount
    FROM
      orders o
    WHERE
      o.customer_id = c.id
  ) all_orders ON TRUE

The shape

Every customer must appear, including those with no orders, so the lateral runs under LEFT JOIN LATERAL ... ON true. For customers with orders, the lateral returns one row per order and the customer row is duplicated; for customers with no orders, the lateral returns zero rows, and LEFT JOIN preserves the customer with NULL values in the order columns. That is the audit's one-row-per-customer-or-pairing shape.

Clause by clause

SELECT c.id, c.name, all_orders.id AS order_id, all_orders.total_amount returns the customer's identity from the outer table and the order's id and amount from the lateral. For an orderless customer, the last two columns are NULL.
FROM customers c is the driving table.
LEFT JOIN LATERAL ( SELECT id, total_amount FROM orders o WHERE o.customer_id = c.id ) all_orders ON true runs once per customer. The correlated filter o.customer_id = c.id scopes the lateral to that customer's orders. LEFT JOIN keeps the outer customer row even when the lateral returns nothing; ON true is the syntactic placeholder PostgreSQL requires for the join condition because the join logic is already encoded inside the lateral.

Why this and not `CROSS JOIN LATERAL`

CROSS JOIN LATERAL drops outer rows whose lateral is empty, which would lose every customer with no orders. The prompt explicitly asks for those customers to appear with NULL order columns, so the join must be LEFT JOIN LATERAL. The two join types are the only meaningful knob on a lateral: pick CROSS JOIN LATERAL when missing matches should drop the outer row, and LEFT JOIN LATERAL ... ON true when missing matches should keep it with NULLs.

The trap

LEFT JOIN LATERAL without ON true is a parse error. The grammar requires a join condition on LEFT JOIN, even though the lateral's correlated filter already does the work. Writing ON true is not a workaround; it is the canonical spelling. Forget it and the editor flags a syntax error, not a row-count bug, which is the easier failure mode to recover from.

You practiced LEFT JOIN LATERAL ... ON true — preserve every outer record, with the lateral contributing multiple rows when matches occur or a single missing-value row otherwise.

Return every customer's ID and name alongside the ID and total amount of each order they have placed. Customers with no orders on record should still appear with missing values in the order columns

The shape

Clause by clause

Why this and not `CROSS JOIN LATERAL`

The trap

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

The shape

Clause by clause

Why this and not CROSS JOIN LATERAL

The trap

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

Why this and not `CROSS JOIN LATERAL`