|
  
|
||
|
|
||
A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.
Define SQL subqueries
A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery can return a set of rows or just one row to its parent query. A scalar subquery is a query that returns exactly one value: a single row, with a single column. Scalar subqueries can be used in most places in a SQL statement where you could use an expression or a literal value.
The places in a query where a subquery may be used are as follows:
| • | In the SELECT list used for column projection |
| • | In the FROM clause |
| • | In the WHERE clause |
| • | In the HAVING clause |
A subquery is often referred to as an inner query, and the statement within which it occurs is then called the outer query. There is nothing wrong with this terminology, except that it may imply that you can only have two levels, inner and outer.
Note: This chapter describes working with Sub-queries used in criteria expressions to limit result dataset (usually in the WHERE and HAVING clauses). To find out how to work with sub-queries used as datasources for the main query (i.e. in the FROM and WITH clauses), please refer to the Derived tables chapter.
You may add a sub-query as part of the expression or condition in the Columns Pane while editing text in a cell. To add a sub-query, right click at the text position for a new sub-query and select the Insert Sub-query item from context popup menu or type the "(Select)" text to the Expression column, the "= (Select)" or "In (Select)" text to the Criteria column. Please note that curly brackets are necessary.
Adding a sub-query
To build a newly added sub-query visually, confirm editing by pressing the Enter key. The corresponding tab is created above the Query Building Area and ellipsis button become visible at the right side of the cell (*). By clicking on the ellipsis button you'll be switched to a sub-query tab where you can build it visually in the same way as the main query. If the cell contains more than one sub-query, the drop-down list will appear to select the necessary sub-query.
(*) The ellipsis button appears only in the VCL and ActiveX Editions of Active Query Builder.
You may always get back to the main query and switch to any sub-query or derived table using tabs above the Query Building Area or using the Query Structure Tree.
Getting access to a sub-query
