Subqueries

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.

Tip

Subqueries are useful for solving complex data retrieval and modification problems; however, this method is often less efficient than performing a join operation.

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)

Examples of subqueries

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'),
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.139.83.62