Subqueries in SQL Server

SubQueries 

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed.

Examples

USE AdventureWorks2008R2;

GO

SELECT Ord.SalesOrderID, Ord.OrderDate,

    (SELECT MAX(OrdDet.UnitPrice)

     FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet

     WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice

FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS Ord

A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

Types of SubQueries:

A correlated sub query is an inner sub query which is referenced by the main outer query such that the inner query is considered as being executed repeatedly.

Example:
----Example of Correlated Subqueries

 
USE AdventureWorks;
GO
SELECT e.EmployeeID FROM HumanResources.Employee e WHERE e.ContactID IN
(
SELECT c.ContactID FROM Person.Contact c WHERE MONTH(c.ModifiedDate) = MONTH(e.ModifiedDate)
)
GO

A noncorrelated subquery is subquery that is independent of the outer query and it can executed on its own without relying on main outer query.

Example:

----Example of Noncorrelated Subqueries
USE AdventureWorks;
GO
SELECT e.EmployeeID  FROM HumanResources.Employee e

WHERE e.ContactID IN
(
SELECT c.ContactID FROM Person.Contact c WHERE c.Title = 'Mr.'
)
GO

Advantages of using subquery

 
Subqueries is a complex queries where  a complex query can be broken down into a series of logical steps for easy understanding and code maintenance.

Subqueries allow you to use the results of another query in the outer query.

In some cases, subqueries can replace complex joins and unions and subqueries are easier to understand.


Disadvantages of using subquery

When subquery is used, the database server (actually the query optimizer) may need to perform additional steps, such as sorting, before the results from the subquery are used. If a query that contains subqueries can be rewritten as a join, you should use join rather than subqueries. This is because using join typically allows the query optimizer to retrieve data in the most efficient way. 

In other words, The optimizer is more mature for MySQL for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.