SQL Subqueries: Queries Within Queries
SQL Subqueries is a SQL query that the outer query checks each product. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Subqueries let you use the result of one query inside another. Essential for filtering, comparisons, and derived tables.
The Formula
"Find products that have never been ordered"
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.
Step-by-Step Breakdown
- Outer query: SELECT from products with an alias p
- WHERE NOT EXISTS — only include rows where the subquery returns nothing
- Subquery: SELECT 1 FROM order_items — checks if any order contains this product
- WHERE oi.product_id = p.product_id — correlates the subquery to the outer row
- SELECT 1 is conventional for EXISTS — the value doesn't matter, only row existence
Edge Cases & Warnings
- 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
Examples
"Product 'Widget A' has 5 orders"
Not in results (has orders)"Product 'Gadget Z' has 0 orders"
Included in resultsFrequently Asked Questions
When should I use EXISTS vs IN?
Use EXISTS for correlated checks (does a match exist?). Use IN for comparing against a list. EXISTS is generally faster when the subquery table is large.
Should I use subqueries or CTEs?
CTEs are more readable for complex multi-step queries. Subqueries are fine for simple filters. Performance is usually identical.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.