FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 

Subqueries

A subquery is a special form of expression that is a query embedded within another query.Subqueries are written in the same way as regular SELECT queries, but they must be enclosed in parentheses.Subquery expressions can be used in the following ways:

  • To specify an output column in the SELECT list

  • To obtain values or conditions for search predicates (the WHERE, HAVING clauses).

  • To produce a set that the enclosing query can select from, as though were a regular table or view.Subqueries like this appear in the FROM clause (derived tables) or in a Common Table Expression (CTE)

Correlated Subqueries

A subquery can be correlated.A query is correlated when the subquery and the main query are interdependent.To process each record in the subquery, it is necessary to fetch a record in the main query, i.e. the subquery fully depends on the main query.

Sample Correlated Subquery
SELECT *
FROM Customers C
WHERE EXISTS
  (SELECT *
   FROM Orders O
   WHERE C.cnum = O.cnum
     AND O.adate = DATE '10.03.1990');

When subqueries are used to get the values of the output column in the SELECT list, a subquery must return a scalar result (see below).