Subqueries
A subquery is a query nested inside another statement. IN is one way to use it, not the definition of a subquery.
Scalar subqueries
A scalar subquery returns one value, such as the overall average sales. Use it where a single value is needed. Ensure it returns at most one row; engines handle multi-row scalar results differently.
IN and EXISTS
IN compares with a list returned by a query. EXISTS asks whether any row exists, so its selected values do not matter. Both can describe membership without multiplying the outer rows.
Correlated subqueries
A correlated query references an outer row, such as o.customer_id = c.customer_id. Conceptually it answers a question for each outer row; actual execution depends on optimizer transformations.
Derived tables and NULL traps
A subquery in FROM supplies a derived table. Give it an alias. NOT IN can produce unexpected unknown comparisons when its result includes NULL; a carefully correlated NOT EXISTS is often clearer.