SQL Subqueries: Queries Within Queries

SELECT p.product_id, p.name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi
  WHERE oi.product_id = p.product_id
);

The outer query checks each product. The subquery searches order_items for that product. NOT EXISTS returns TRUE when the subquery finds zero rows — meaning the product has never been ordered.

1. Outer query: SELECT from products with an alias p
2. WHERE NOT EXISTS — only include rows where the subquery returns nothing
3. Subquery: SELECT 1 FROM order_items — checks if any order contains this product
4. WHERE oi.product_id = p.product_id — correlates the subquery to the outer row
5. SELECT 1 is conventional for EXISTS — the value doesn't matter, only row existence

• NOT IN fails with NULLs: if the subquery returns any NULL, NOT IN returns no rows. Use NOT EXISTS instead.
• Correlated subqueries run once per outer row — can be slow on large tables
• EXISTS stops at the first match — more efficient than COUNT(*) > 0
• For readability on complex subqueries, consider converting to CTEs