A subquery is a query that is nested inside a SELECT
, INSERT
, UPDATE
, or DELETE
statement, or inside another subquery. Subqueries are often used in situations where a query depends on the results of another query. SQL Server supports noncorrelated and correlated subqueries.
In a noncorrelated subquery, the inner query is independent and gets evaluated first, then passes results to the outer query. A noncorrelated (independent) subquery can be independently evaluated and relies only on its own SELECT
clause for instructions.
In a correlated subquery, the outer query provides values for the dependant inner subquery evaluation. SQL Server passes the subquery results back to the outer query for evaluation. A correlated (dependant) subquery receives values from the outer SELECT
statement.
Typically, the statements that include subqueries take one of three forms. The first form is to use a comparison operator in the WHERE
clause, as follows:
WHERE expression comparison_operator [ANY | ALL] (subquery)
When ALL
is specified, SQL Server evaluates the expression as true if it is true for either all rows or none of the rows. When ANY
is specified, SQL Server evaluates the expression to true if the expression is true for at least one row of a subquery.
In the second form, we use the IN
keyword (or NOT IN
) in the WHERE
clause of the outside query as follows:
WHERE expression [NOT] IN (subquery)
In the third form, we use the EXISTS
(or NOT EXISTS
) keyword in the WHERE
clause as follows:
WHERE expression [NOT] EXISTS (subquery)
The following query returns the names of all employees who have a valid e-mail address:
SELECT [Title] , [FirstName] , [MiddleName] , [LastName] FROM [Person].[Person] p WHERE EXISTS ( SELECT * FROM [Person].[EmailAddress] e WHERE p.[BusinessEntityID] = e.[BusinessEntityID] );
To return a list of all customers who live in territories that are not covered by any salesperson, we will execute the following T-SQL code:
SELECT * FROM [Sales].[Customer] WHERE [TerritoryID] <> ANY (SELECT [TerritoryID] FROM [Sales].[SalesPerson]);
The following query finds the CustomerID
and AccountNumber
values of all customers who live in Europe:
SELECT [CustomerID] , [AccountNumber] FROM [Sales].[Customer] WHERE [TerritoryID] IN ( SELECT [TerritoryID] FROM [Sales].[SalesTerritory] WHERE [Group] = 'Europe'),
3.139.83.62