Lesson 28 / 37 · Composing queries

A subquery that stands alone runs first

The IN subquery and the scalar subquery do not mention the outer query, so each runs once and gets its own pipeline under the WHERE step. Open them to see the list and the single value WHERE compares against.

The query

SELECT p.name, p.category, p.price
FROM products p
WHERE p.id IN (SELECT product_id FROM order_items WHERE qty > 1)
  AND p.price < (SELECT avg(price) FROM products)
ORDER BY p.price DESC

Try qty > 0 in the IN subquery: every ordered product qualifies, and 4 products pass.

Next: EXISTS asks a question per row · All lessons