A subquery is a query embedded within another query, typically in an expert clause such as a Where clause, and typically occurring in conjunction with an EXISTS, IN, LIKE, OVERLAPS or standard comparison operator. A special Subquery SQL element is provided in category Other and in category Other – Expert which may be used after the following preliminary steps are taken.
- Build a separate Variable Creation analysis for the subquery.
- If a Variable Creation analysis is being used to define the subquery, check the Subquery option on the analysis parameters tab of the subquery analysis.
- Set the output style of the subquery analysis to notStore the tabular output of this analysis in the database.
- Set the Output - storage option to Generate the SQL for this analysis but do not execute it in the subquery analysis to embed the SQL in the containing analysis (not required unless it is to be a correlated subquery).
- A subquery may be made a correlated subquery by making the subquery analysis specifically refer to an input table in the containing analysis through the use of a unique table alias prefix. For more information, please refer to Variable Creation - INPUT - Expert Options - SQL Elements.
A subquery analysis may be referred to in a Variable Creation analysis, or if Teradata 13.00 or later is in use, in a Derive transformation in a Variable Transformation analysis. Simply place the Subquery SQL element in the appropriate place in an expert clause, variable, dimension or join clause search condition, and then drag any column produced by the subquery analysis under the added subquery element. For more information, refer to Variable Creation - INPUT - Variables - SQL Elements or Variable Creation - INPUT - Expert Options - SQL Elements.
- Columns referenced in a subquery do not affect column limiting when refreshing or publishing an analysis.
- Columns referenced in a subquery are treated as “contributing columns” in output advertising.
- Columns referenced in a subquery occurring in a Variable Creation that creates a score table are not treated as Model Variables.
- Anchor table replacement does not occur in subquery analyses, even if requested by selecting anchor columns. However, anchor table replacement may be requested in analyses referred to by a subquery.
- Literal parameters may be included in a subquery or in any analyses they reference.
- In addition to their use in Where, Having and Qualify clauses, subqueries may occur in various other “expert” clauses such as Group By, Order By and Sample in limited ways.
- A subquery cannot be used in a Top clause or in the size portions of a Sample clause (though they may be used in a stratified condition in a Sample clause).
- In a With Recursive query, a subquery may be used in the base query or seed query but not in the recursive portion of the query.
- In a With Recursive View, a subquery may be used in the seed query or appear as part of the statement that selects from the view (equivalent to the base query).