Problem
A subquery is a SELECT statement embedded in another SQL statement, such as a SELECT, INSERT, DELETE, or UPDATE statement. The set of value(s) returned by the inner SELECT statement are passed to the outer SQL statement. The inner SELECT statement is always embraced in parentheses. The result set from the inner SELECT statement is an example of a temporary data store. Subqueries can reside at many different places within an outer SELECT statement. This tutorial section introduces the topic of subqueries by presenting easy-to-follow code samples that demonstrate the use of subqueries in WHERE clauses, SELECT list items, and with INSERT, UPDATE, and DELETE statements. Two subsequent sections drill down on more advanced issues pertaining to subqueries.
Solution
For the examples below we are using the AdventureWorks2014 database. Download a copy and restore to your instance of SQL Server to test the below scripts.
Using a Subquery in a SQL Server SELECT Statement WHERE Clause
One of the most common places to invoke a subquery is in the WHERE clause of a SELECT statement. The inner query may come from the same source or a different source as the outer SQL statement. When the inner query needs to be computed for each row in the outer query, then the inner query is a correlated subquery. Otherwise, the inner query is an uncorrelated subquery. A subsequent tutorial section contrasts correlated and uncorrelated subqueries.
An inner subquery in a WHERE clause can return one or more rows. A typical usage compares subquery result set rows with an outer SELECT query’s result set. The outcome of the comparisons determines which rows are returned from outer query with the subquery constraint. In this tip, the focus will be on basing comparisons on equality (or inequality) as well as membership in a set using the in operator. However, there are many kinds of operators for comparing outer query column values to inner query return values. Among these are: =, !=, >, >=, <, <= , in, not in, exists, not exists, any, all.
The code sample below illustrates the use of a subquery that returns a single row that is compared to an outer SELECT statement by an = operator. The code sample uses the Adventureworks2014 database, which you can download with the link above.
- The subquery appears at the end of the script in parentheses. This very simple example returns the ProductCategoryID value from the ProductCategory table in the Production schema. The where clause in the subquery’s SELECT statement determines which ProductCategoryID value is returned from the subquery.
- For the example below, the returned ProductCategoryID value from the subquery is 1, but you can change this value by modifying the WHERE clause in the subquery.
- The source for the outer query is an inner join of the ProductCategory and ProductSubcategory tables from the Production schema in the Adventureworks2014 database.
- The two tables are joined by ProductCategoryID values from the two tables.
- The outer query can return three columns of data: ProductCategoryID and Name from the ProductCategory table as well as Name from the ProductSubcategory table.
- The WHERE clause in the outer SELECT statement compares ProductCategoryID values from the outer SELECT statement with the ProductCategoryID value returned from the subquery. When the ProductCategoryID value from the outer query equals the ProductCategoryID value from the subquery, then the corresponding row from the outer query becomes a row in the overall result set.
-- a subquery in a where clause that returns 1 row
SELECT
cat.ProductCategoryID,
cat.Name cat_name,
subcat.Name subcat_name
FROM [AdventureWorks2014].[Production].[ProductCategory] cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcat
ON cat.ProductCategoryID = subcat.ProductCategoryID
WHERE cat.ProductCategoryID =
-- the code in parentheses is the subquery
( SELECT cat.ProductCategoryID
FROM [AdventureWorks2014].[Production].[ProductCategory] cat
WHERE cat.ProductCategoryID = 1
)The following Results tab shows the values returned by the preceding script. Notice that there are three rows returned. All returned rows have a ProductCategoryID value of 1, which matches the subquery constraint value. The columns in the result set show select list items in the outer SELECT statement with a subquery constraint from within a WHERE clause.
The next example of a subquery in a WHERE clause is for a subquery that returns more than one row. There are several ways to code this kind of solution. The example below works well for a subquery that returns numeric values where the order of the numeric values determines the range of values to be returned by the outer query.
- The first query in the following script returns 37 rows; this query is the outer query without a subquery constraint in a WHERE clause. Each row is for a distinct ProductSubcategory row. The subcat_name column denotes the Name column values from the ProductSubcategory table.
- The next query includes a subquery constraint for the outer query. This query returns just 20 rows. Each of these rows has a ProductCategoryID value from the ProductCategory table of either 3 or 4.
- The subquery constraint allows the inclusion of all rows from the outer query where the ProductCategoryID value is either
- greater than or equal 3 and
- less than or equal to 4
- The minimum and maximum ProductCategoryID constraint values are assigned in a declare statement for the @cat_id_min and @cat_id_max local variables.
- By referring to the output from the first subquery without a subquery constraint, you can verify that there are eight rows with a ProductCategoryID value of 3 and an additional twelve rows with a ProductCategoryID value of 4 for a total of 20 ProductSubcategory rows with ProductCategoryID values of 3 or more and less than or equal to 4.
-- outer query without a subquery constraint
-- there are 37 rows returned by the query
SELECT
cat.ProductCategoryID cat_id,
cat.Name cat_name,
subcat.Name subcat_name
FROM [AdventureWorks2014].[Production].[ProductCategory] cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcat
ON cat.ProductCategoryID = subcat.ProductCategoryID
-- a subquery in a where clause that can return more than 1 row
-- the subquery constraint causes the outer query to return 20 rows
-- with ProductCategoryID values of 3 and 4
DECLARE @cat_id_min int = 3,
@cat_id_max int = 4
SELECT
cat.ProductCategoryID cat_id,
cat.Name cat_name,
subcat.Name subcat_name
FROM [AdventureWorks2014].[Production].[ProductCategory] cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcat
ON cat.ProductCategoryID = subcat.ProductCategoryID
WHERE cat.ProductCategoryID IN
-- the code in parentheses is the subquery
( SELECT cat.ProductCategoryID
FROM [AdventureWorks2014].[Production].[ProductCategory] cat
WHERE cat.ProductCategoryID >= @cat_id_min
AND cat.ProductCategoryID <= @cat_id_max
)To help clarify the operation of the code, the two result sets from the preceding script are displayed and described.
- The first Result tab shows the result set from the query without a subquery constraint.
- Notice there are 37 rows in total.
- Also, notice that there are
- Eight rows with a ProductCategoryID value of 3
- Twelve rows with a ProductCategoryID value of 4
- The second Result tab shows the result set from the outer query with a subquery constraint.
- For this result set there are just 20 rows.
- The first eight rows are for those with a ProductCategoryID value of 3.
- The next twelve rows are those with a ProductCategoryID value of 4.
When the subquery constraint specifies with string values instead of numeric values, then you can use comparison operators that do not require numbers. The following syntax shows how to reference a subquery from an outer query with the any operator.
- In the following code block, the Name field value from the ProductCategory table is compared via the any operator to a set of category name values in the subquery.
- Whenever the Name column value from the ProductCategory table for the join of the ProductCategory and ProductSubcategory tables matches any of the category name values returned by the subquery, then the SELECT field values for that row from the join is passed to the result set for the outer query with the subquery constraint.
- You can control the result set by adding and removing category name values from the subquery.
- Because Bikes and Accessories are listed in the subquery, the following code block returns all rows from the joined ProductCategory and ProductSubcategory tables with a Name column value of Bikes or Accessories in the Name column of the ProductCategory table.
-- a subquery in a where clause for a subset of items
-- demonstrate any operator application
SELECT
cat.ProductCategoryID cat_id,
cat.Name cat_name,
subcat.Name subcat_name
FROM [AdventureWorks2014].[Production].[ProductCategory] cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcat
ON cat.ProductCategoryID = subcat.ProductCategoryID
WHERE cat.Name = ANY
-- the code in parentheses is the subquery
( SELECT cat.Name
FROM [AdventureWorks2014].[Production].[ProductCategory] cat
WHERE cat.Name IN ('Bikes', 'Accessories')
)
The following Result tab displays the result tab from the preceding query.
- All rows in the result set have a cat_name value of either Accessories or Bikes. This derives from the combination of the subquery constraint and the any operator in the WHERE clause at the conclusion of the outer query.
- There are 15 rows in the result set:
- Twelve with a cat_name value of Accessories and
- another three with a cat_name value of Bikes
Using a Subquery as a SELECT List Item
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.
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.
Using a SQL Server Subquery for INSERT, UPDATE, and DELETE Statements
Up until this point, the tutorial demonstrated how to use subqueries with a WHERE clause in a SELECT statement as well as in select list items. This concluding subsection on introducing subqueries presents examples for using subqueries in INSERT, UPDATE, and DELETE statements.
The first demonstration is for the populating of a local temp table with a bulk insert statement based on a SELECT statement with subquery. The code sample below starts by creating a fresh copy of the #bikes_subcategories local temp table. The table has three columns named ProductCategoryID, cat_name, and subcat_name.
After creating the #bikes_subcategories local temp table, the code invokes a bulk insert statement based on a SELECT statement with a subquery. The subquery extracts the ProductCategoryID with a value of 1 from the ProductCategory table in the Production schema of the AdventureWorks2014. ProductCategoryID value 1 denotes the bikes category. This category has three subcategories with names of Mountain Bikes, Road Bikes, and Touring Bikes.
The outer query referencing the subquery has a source based on the join of the ProductCategory and ProductSubcategory tables in the Adventureworks2014 database. The outer query select list items include ProductCategoryID, cat_name from the Name column in the ProductCategory table, and subcat_name from the Name column in the ProductSubcategory table. A where clause in the outer query extracts rows from the join when their ProductCategoryID value equals the value returned by the subquery, which is 1 in the sample below.
-- create fresh version of #bikes_subcategories tables
BEGIN TRY
--drop table #MaleStudents --#bikes_subcategories
DROP TABLE #bikes_subcategories
END TRY
BEGIN CATCH
PRINT '#bikes_subcategories not available to drop'
END CATCH
GO
CREATE TABLE #bikes_subcategories (
ProductCategoryID int,
cat_name nvarchar(50),
subcat_name nvarchar(50)
)
-- insert into temp table from
-- a select statement with a subquery
INSERT INTO #bikes_subcategories
-- a subquery in a where clause that returns 1 row
SELECT
cat.ProductCategoryID,
cat.Name cat_name,
subcat.Name subcat_name
FROM [AdventureWorks2014].[Production].[ProductCategory] cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcat
ON cat.ProductCategoryID = subcat.ProductCategoryID
WHERE cat.ProductCategoryID =
-- the code in parentheses is the subquery
( SELECT cat.ProductCategoryID
FROM [AdventureWorks2014].[Production].[ProductCategory] cat
WHERE cat.ProductCategoryID = 1
)
-- display rows inserted into #bikes_subcategories
SELECT * FROM #bikes_subcategories
The next screen shot shows the three rows returned from a SELECT statement for all columns in the #bikes_subcategories local temp table.
- The three rows in the Results tab below all have a ProductCategoryID value of 1; this is the value returned by the subquery in the preceding script.
- The ProductCategoryID value of 1 points to a category named Bikes. Therefore, the cat_name column value is Bikes for all rows in the table.
- The subcat_name column displays subcategory names for the Bikes category. The subcategory names are: Mountain Bikes, Road Bikes, and Touring Bikes.
The next code sample shows how to use a subquery with an UPDATE statement. The UPDATE statement changes the name of the subcategory value for a row in a table. The following code sample assigns the value of Racing Bikes to the subcat_name column in the #bikes_subcategories local temp table. The UPDATE keyword points to the temp table, and the set statement identifies the column to which to assign Racing Bikes. The where keyword accepts a filter based on the subquery; this filter identifies rows in which to perform the update.
-- update 'Road Bikes' subcat_name to 'Racing Bikes'
-- subcat_name from #bikes_subcategories
UPDATE #bikes_subcategories
SET subcat_name = 'Racing Bikes'
WHERE subcat_name IN ( SELECT Name subcat_name
FROM [AdventureWorks2014].[Production].[ProductSubcategory]
WHERE [ProductCategoryID] = 1
AND Name = 'Road Bikes'
)
-- display #bikes_subcategories after update
SELECT * FROM #bikes_subcategories
The following Results tab shows the three rows from the local temp table after the update statement. By comparing this Result tab to the preceding Result tab, you can verify the operation of the UPDATE statement based on the subquery. Notice that the second row has a subcat_name value of Racing Bikes in the following Result tab, but the subcat_name value for the second row is Road Bikes.
The final example for this introduction to subqueries tutorial section demonstrates the use of a subquery in combination with a DELETE statement. The code sample below removes a row from the #bikes_subcategories local temp table. The subquery designates the row to delete, and the DELETE keyword points at the temp table from which to delete the row. The subquery specifies the row with a subcat_name value of Racing Bikes. The DELETE statement removes the one row in the #bikes_subcategories local temp table with a value of Racing Bikes.
-- delete 'Road Bikes' subcat_name from #bikes_subcategories
DELETE #bikes_subcategories
WHERE subcat_name IN ( SELECT subcat_name
FROM #bikes_subcategories
WHERE [ProductCategoryID] = 1
AND subcat_name = 'Racing Bikes'
)
-- display rows remaining #bikes_subcategories
SELECT * FROM #bikes_subcategories
Here’s a Results tab that shows the rows in the #bikes_subcategories local temp table after the operation of the delete statement. Because one row is deleted, there are now two rows instead of three rows in the table. Notice it is the row with a subcat_name value of Racing Bikes that is removed from the temp table.
Next Steps
Here are some links to resources that you may find useful to help you grow your understanding of content from this section of the tutorial.
