Databases
Subqueries (a query inside a query)
O(n) to compute the subquery's value once, plus O(n) to scan for the outer filter. This is O(n) total, as long as you compute the inner value once and reuse it.
The idea, in plain English
A subquery answers a question by first working out a smaller question. 'Which orders were above average?' needs the average first. So you compute that inner answer, then use it to answer the outer question. It is a query nested inside another query's WHERE clause.
How it works
- 1First, think of the inner query as running on its own, such as SELECT AVG(amount) FROM orders. It produces a single number.
- 2Plug that number into the outer query's condition, such as WHERE amount > (that number).
- 3The outer query then runs like any normal filter, using the value the subquery produced.
When you'd use it
Use a subquery whenever a condition depends on something you must compute from the data first. For example: 'above average', 'the most expensive item', or 'customers who placed at least one order over $100'.
Common beginner mistakes
- Do not recompute the subquery once for every outer row. Compute it once and reuse the result. Recomputing it silently turns an O(n) job into O(n²).
- Do not use plain floating-point division for an average and expect it to print identically everywhere. Tiny floating-point differences can appear across languages. Here we use integer (floor) division to keep the result exactly reproducible.
Try it — edit and run
Click the code to edit · press ⌘/Ctrl+↵ to run
Editable code. Tab and Shift+Tab indent. Press Escape, then Tab, to move focus out of the editor.
SQL: SELECT AVG(amount) FROM orders
Average amount: 20
SQL: SELECT item, amount FROM orders WHERE amount > (SELECT AVG(amount) FROM orders)
Lamp 25
Chair 40Not sure this is the right topic? See the learning paths → or where this leads →