Complex query tasks can be accomplished by nesting queries within a queries. While this approach reduces the number of queries compared to stacked queries, nested queries are more difficult to understand and are therefore generally not recommended. The following example shows a nested query that accomplished the same task as the stacked queries here.

EXAMPLE DATABASE: Nhanes

DATA INPUT

In the table nhanes, the column fseqn contains an identification number of the family to which the respondent belongs. There may be more than one respondent per family and we may be interested in family sizes, or more accurately the number of respondents per family.

QUERY family_size_freq2

SELECT familysize, Count(*) AS frequency
FROM [SELECT fseqn, count(*) AS familysize 
      FROM nhanes 
      GROUP BY fseqn]. AS temp
GROUP BY familysize
ORDER BY familysize;

Query family_size_freq2 uses the table nhanes as input and directly produces a frequency count of the different family sizes (respondents per family).

DISCUSSION

The inner query, enclosed in square brackets, produces a count of respondents (familiysize) for each unique family identification number (fseqn). The outer query uses this new column (familysize) to again produce a fequency count. The inner query will be automatically run first before the outer query. Note the special syntax for inner queries, e.g. the enclosure in square brackets followed by a dot and the assignment of a name (AS temp) to the inner query.

The output shows 18 different family sized, ranging from 1 to 20, and their respective frequency of occurrence in this survey. For more than half of the families, there was only one respondent in this survey.

The above example is a very simple example of a nested query. If more queries and nesting levels are involved, nested queries can become very complex and are therefore generally not recommended. If a nested query is required, it is recommended to develop it first as a stacked query and then combine it into a nested query. The stacked version and the nested version should then be compared to verify that they produce identical results.