Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

IntermediateSubqueries & EXISTS

Subquery in SELECT: each order with its item count

The query

SQL
SELECT
  o.order_id,
  o.order_date,
  o.total,
  (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.order_id) AS item_count,
  (SELECT MAX(unit_price) FROM order_items oi WHERE oi.order_id = o.order_id) AS max_item_price
FROM orders o
ORDER BY o.order_date DESC;
Tested against PostgreSQL 16

Note

“Subquery in SELECT list (scalar subquery). Each runs per row. Consider JOIN+GROUP for performance.

Tables referenced