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. | 
