A subquery is a SELECT statement nested, or embedded, within another query. The nested query (subquery), is the inner query. The query containing the nested query is the outer query. A subquery can be used anywhere an expression is allowed, as a column expression, in a WHERE clause, in a HAVING clause or even in a FROM clause.
The purpose of a subquery is to return results to the outer query.
Consideration of Subquery:
To donate a query as a subquery, enclose it in parentheses. Multiple level of subqueries are supported in SQL server up to 32 levels which is not recommended as it increases the query complexity and Optimizer might not be able to convert them to Inner Join.
If the subquery returns an empty result set, the result of the subquery is converted and returned as a NULL. Ensure your outer query can gracefully handle a NULL, in addition to other expected results.
The form of the results will determine whether the subquery is a scalar or multi-valued subquery:
- Scalar subqueries, like scalar functions, returns a single value. Outer queries need to be written to process a single result.
- Multi-Valued subqueries return a result much like a single-column table. Outer queries need to be written to handle multiple possible results.
There are two kind of subqueries, Self-Contained Subquery and Correlated Subquery which is explained on the followings:-
Can be written as stand-alone queries, with no dependencies on the outer query. A self-contained subquery is processed once, when the outer query runs and passes its results to that query. It can return its result as scalar or multi-valued.
Select top 1 sod.OrderQty , soh.Salesorderid , sod.SalesOrderDetailID , soh.TotalDue , soh.OrderDate from sales.salesorderheader as soh inner join sales.salesorderdetail as sod on soh.salesorderid=sod.salesorderid Where sod.salesorderid = ( Select sod.salesorderid From sales.salesorderdetail as sod Where sod.OrderQty = (Select max (sod.OrderQty) From sales.salesorderdetail as sod)) order by OrderQty desc
A multi-valued subquery is well suited to return results to the IN predicate, as in the following example:
Select SalespersonId , TerritoryId , Sum(TotalDue) as TotalDue from Sales.Salesorderheader
Where SalespersonId in ( Select BusinessEntityId from Sales.Salesperson Where SalesYTD>2000000)
Group By SalespersonId , TerritoryId
Correlated subqueries are SELECT statements nested within an outer query. They may also be written as scalar or multi-valued subqueries. They are typically used to pass a value from the outer query to the inner query, to be used as a parameter there. In correlated subqueries, the inner query receives input from the outer query and conceptually executes once per row in it. Correlated subqueries cannot be executed separately from the outer query. This complicates testing and debugging. Unlike self-contained subqueries which are processed once, correlated subqueries will run multiple times. Logically, the outer query runs first, and for each row returned, the inner query is processed.
Select SalesPersonID ,(Select FirstName+’ ‘+LastName From person.person as pp
Where pp.BusinessEntityID=soh.salespersonid) as FullName,
Sum(TotalDue) as TotalDue ,
(select top 1 year(orderdate) from sales.salesorderheader where orderdate=’2005-10-15′) as OrderYear
From sales.salesorderheader as soh
Group by salespersonid
[info]It is better to minimize using subqueries and replace them with Inner Join or other type of joins to increase performance. [/info]
Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases, a join approach would yield better results. The following is an example showing both a subquery SELECT and a join SELECT that return the same result set:
Where SalesPersonID IN (Select BusinessEntityId From Person.Person Where FirstName =N’David’)
Group By SalesPersonID , Year(OrderDate)
Inner join Person.Person as PP on SOH.SalesPersonID=PP.BusinessEntityID
Where PP.FirstName =N’David’
Group By SalesPersonID , Year(OrderDate)
Using The EXIST Predicates With Subqueries
SQL Server provides a mechanism for checking whether any results would be returned from a query. The EXISTS predicate evaluates whether rows exist, but rather than return them, it returns TRUE or FALSE. This is a useful technique for validating data without incurring the overhead of retrieving and counting the results.
Compare the following queries:-
Where ( Select Count(*) From Sales.SalesOrderDetail as SOD
Where EXISTS ( Select * From Sales.SalesOrderDetail as SOD
In the first example, the subquery must count every occurrence of each ProuductID found in the Sales.SalesOrderDetail table, and compare the count results to zero, simply to indicate that the Product has associated orders.
In the second query, EXISTS returns TRUE for an ProductID as soon as one has been found in the Sales.SalesOrderDetail table—a complete accounting of each occurrence is unnecessary.
Another useful application of EXISTS is negating it with NOT, as in the following example, which will return any Product which has never sold.
Select PP.ProductID , PP.[Name] , PP.ListPrice From Production.Product as PP
Where NOT EXISTS ( Select * From Sales.SalesOrderDetail as SOD
To write queries that use EXISTS with subqueries, consider the following guidelines:
The keyword EXISTS directly follows WHERE. No column name (or other expression) needs to precede it, unless NOT is also used.
Within the subquery following EXISTS, the SELECT list only needs to contain (*). No rows are returned by the subquery, so no columns need to be specified.
I hope this blog has been able to attract your attention and informative enough.