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:

  1. Scalar subqueries, like scalar functions, returns a single value. Outer queries need to be written to process a single result.
  2. 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:-

SELF-CONTAINED SUBQUERY

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.

[codesyntax lang=”tsql”]

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

[/codesyntax]

A multi-valued subquery is well suited to return results to the IN predicate, as in the following example:

[codesyntax lang=”tsql”]

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

[/codesyntax]

Correlated Subquery

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.

[codesyntax lang=”tsql”]

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

[/codesyntax]

[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:

[codesyntax lang=”tsql”]

 Select SalespersonId , Sum(TotalDue) as TotalDue , Year(Orderdate) as [Year] From Sales.Salesorderheader
Where SalesPersonID IN (Select BusinessEntityId From Person.Person Where FirstName =N’David’)
Group By SalesPersonID , Year(OrderDate)
 
 Select SalespersonId , Sum(TotalDue) as TotalDue , Year(Orderdate) as [Year] From Sales.Salesorderheader as SOH
Inner join Person.Person as PP on SOH.SalesPersonID=PP.BusinessEntityID
Where PP.FirstName =N’David’
Group By SalesPersonID , Year(OrderDate)

[/codesyntax]

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:-

[codesyntax lang=”tsql”]

 Select PP.ProductID , PP.[Name] , PP.ListPrice From Production.Product as PP
Where ( Select Count(*) From Sales.SalesOrderDetail as SOD
Where PP.ProductID=SOD.ProductID)>0
 
Select PP.ProductID , PP.[Name] , PP.ListPrice From Production.Product as PP
Where EXISTS ( Select * From Sales.SalesOrderDetail as SOD
Where PP.ProductID=SOD.ProductID)

[/codesyntax]

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.

[codesyntax lang=”tsql”]

Select PP.ProductID , PP.[Name] , PP.ListPrice From Production.Product as PP
Where  NOT EXISTS ( Select * From Sales.SalesOrderDetail as SOD
Where PP.ProductID=SOD.ProductID)

[/codesyntax]

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.

 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan