A subquery is a SELECT statement nested inside another SQL statement. It can return one value, a list of values, or a table-shaped result. Choosing the right form depends on what the outer query needs: a scalar value for comparison, a set for membership, or a derived table for a later step.
Key Takeaways at a Glance:
- •A Scalar Subquery returns exactly 1 value (1 row, 1 column) and can be used anywhere an expression is expected (SELECT, WHERE).
- •A Multi-Row Subquery returns a single column of multiple rows, used with IN, ANY, or ALL.
- •A Derived Table is a subquery in the FROM clause and MUST be given an alias in ANSI SQL.
- •If a scalar subquery returns more than 1 row at runtime, the query aborts with a fatal 'Subquery returns more than 1 row' error.
In Simple Words (The Mental Model)
A subquery is just a query inside another query. The database runs the inner query first to get an answer (like finding the average salary), and then plugs that answer into the outer query (like finding all employees who earn more than that average).
Real-World Analogy
Think of asking your friend: 'Go check the thermometer outside and tell me the temperature; then I will decide whether to wear a jacket'. The friend checking the thermometer is the inner subquery; you deciding whether to put on a jacket is the outer query.