Using a subquery as a select list item

By: Rick Dobson

Overview

The code samples in this section illustrate two different approaches to using a subquery as a select list item.  The first example demonstrates how to use a subquery that returns a constant value as a select list item.  The second example presents a technique in which the subquery return value can vary depending on other select list items.

Using a subquery as a select list item

The first type of query is called an uncorrelated subquery.  This is because the value returned by the subquery does not depend on other SELECT list items.  This uncorrelated subquery list item returns the same value for all rows in the result set.

The following code block demonstrates the syntax for using an uncorrelated subquery as a select list item.  The outer query in the following code block returns a result set with four columns.

• The first three columns are field values based on the join of the ProductCategory and ProductSubcategory tables.  The alias name for the ProductCategory table is outer_cat, and the alias name for the ProductSubcategory table is subcat.
  • The first column in the result set is the ProductCategoryID field from the ProductCategory table; this column has an alias of name of cat_id.
  • The second column in the result set is the Name field from the ProductCategory table.  Its alias name is outer_cat_name.
  • The third column in the result set is the Name field from the ProductSubcategory table.  Its alias name is subcat_name.
• The fourth column in the outer select statement is a subquery that returns the same value for all rows.  The subquery consists of a count function for the ProductSubcategoryID values in the ProductSubcategory table.  The alias name for this select list item is total_sub_cat_count.

-- a select list item based on an uncorrelated subquery
SELECT
  outer_cat.ProductCategoryID cat_id,
  outer_cat.Name outer_cat_name,
  subcat.Name subcat_name,
  (SELECT
     COUNT(ProductSubcategoryID) subcat_id_count
   FROM [AdventureWorks2014].[Production].[ProductCategory] outer_cat
   INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcat
     ON outer_cat.ProductCategoryID = subcat.ProductCategoryID
  ) total_sub_cat_count
FROM [AdventureWorks2014].[Production].[ProductCategory] outer_cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcat
  ON outer_cat.ProductCategoryID = subcat.ProductCategoryID

The following Result tab image shows the result set from the preceding code sample.

• The first three columns are derived from the join of the ProductCategory and ProductSubcategory tables.
• The fourth column has the same value for all rows.  This value is the count of the ProductSubcategoryID values in the ProductSubcategory table.  The values for the fourth column do not depend on any other column value in a row.

The next code sample adds a new subquery to the preceding code sample.  This new subquery demonstrates the syntax for a correlated subquery used as a list item.

• The new subquery in the code sample below has the alias name of sub_cat_count.
• The subquery for this select list item returns the count of ProductSubcategoryID values in the join of the ProductCategory and ProductSubcategory tables grouped by category name. 
• The HAVING clause within the GROUP BY clause for the count function inside the subquery links the Name column from the ProductCategory table for the inner query to the Name column value from the ProductCategory table in the outer query (having cat.name = outer_cat.Name).
• It is because of the HAVING clause setting that the subquery is a correlated subquery.  The correlation is between the category name values from the subquery and the category name values from the outer query.

-- select list items with uncorrolated and corrolated subqueries
SELECT
  outer_cat.ProductCategoryID cat_id,
  outer_cat.Name outer_cat_name,
  subcat.Name subcat_name,
  (SELECT
     COUNT(ProductSubcategoryID) subcat_id_count
   FROM [AdventureWorks2014].[Production].[ProductCategory] outer_cat
   INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcat
     ON outer_cat.ProductCategoryID = subcat.ProductCategoryID
  ) total_sub_cat_count,
  (SELECT
     COUNT(ProductSubcategoryID) subcat_id_count
   FROM [AdventureWorks2014].[Production].[ProductCategory] cat
   INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcat
     ON cat.ProductCategoryID = subcat.ProductCategoryID
   GROUP BY cat.name
   HAVING cat.name = outer_cat.Name
  ) sub_cat_count
FROM [AdventureWorks2014].[Production].[ProductCategory] outer_cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcat
  ON outer_cat.ProductCategoryID = subcat.ProductCategoryID

The next screen shot shows the result set from the preceding code block.

• The first four columns are the same as in the code sample for the uncorrelated subquery. 
• The fifth column derives its values from the correlated subquery named sub_cat_count.  Consequently, the fifth column in the result set has the name sub_cat_count.
• The values for the fifth column vary by category name (outer_cat_name) in the outer query.  In contrast, all the values for the fourth column are the same (37). 
  • The values showing in the fourth column are the total count of ProductSubcategoryID values within the join of the ProductCategory and ProductSubcategory tables.
  • The values showing the fifth column are the count of  ProductSubcategoryID values within each outer_cat_name value.