A subquery is a query within a query. You can use subqueries to formulate a range of questions in SQL that are either difficult or impossible to represent using WHERE clauses and JOIN commands. The following sections provide some examples of how subqueries can be applied.
The stored procedure in the following example contains two subqueries that are used to determine values for columns:
CREATE PROCEDURE "FilmLength" AS SELECT Filmtitle, 'First Week'=(SELECT MIN(wo.Week) FROM tblWeeks wo WHERE wo.Filmnr = tblFilms.Filmnr), 'Last Week'=(SELECT MAX(wo.Week) FROM tblWeeks wo WHERE wo.Filmnr = tblFilms.Filmnr) FROM tblFilms RETURN
If you use a subquery within a WHERE clause, you have three ...