In [SQL] a subquery is a [query] within another query. The results of the subquery are used in the [predicate] of the main, or outer, query to produce the output.

Here is a simple [example].

SELECT * FROM movies outer
   WHERE outer.director IN 
     (SELECT inner.name FROM directors inner
      WHERE inner.nationality = "British");

In this query, [everything] within the [parentheses] is considered the subquery. What happens first is that the query [engine] selects the first row in the movies table. It then executes the subquery, which results in a listing of all the British directors in the directors table. The outer query's [predicate] is then evaluated against this list, and if the candidate row from the outer table has a director that matches the list from the subquery, then that movie is returned as output.

This is how subqueries work in [theory]. There is some variation from vendor to vendor on how the subquery is actually processed by the query engine. Some vendors, I believe, have [optimization] features so that the subquery is only executed once instead of once per candidate row. Obviously this results in much faster [execution].

subquery (idea)