Sub Query Fundamentals – shannonlowder.com

Here we are with another sub query post.

A sub query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub query. A sub query can be used anywhere an expression is allowed. Here’s an example of a sub query in the SELECT clause of a SQL statement.

USE AdventureWorks
GO
SELECT Ord.SalesOrderID, Ord.OrderDate,
    (SELECT MAX(OrdDet.UnitPrice)
     FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet
     WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM AdventureWorks.Sales.SalesOrderHeader AS Ord

You may hear people refer to a sub query as an inner query or inner select. In those cases, you may also hear the statement containing a sub query referred to as an outer query or outer select.

Nearly every sub query statement can be rewritten as joins. In Microsoft SQL, there is usually no performance difference between a statement that includes a sub query and a the join. But if you are choosing to use a sub query, and the performance is less than flawless, test the performance of the JOIN.

The following is an example showing both a sub query SELECT and a join SELECT that return the same result set:

/* SELECT statement built using a sub query. */
SELECT Name
FROM AdventureWorks.Production.Product
WHERE ListPrice =
    (SELECT ListPrice
     FROM AdventureWorks.Production.Product
     WHERE Name = 'Chainring Bolts' )

/* SELECT statement built using a join that returns
   the same result set. */
SELECT Prd1. Name
FROM AdventureWorks.Production.Product AS Prd1
     JOIN AdventureWorks.Production.Product AS Prd2
       ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2. Name = 'Chainring Bolts'

In this case, the estimated execution plan shows these two queries have the same cost. That means they should have the same run times, and performance use. But when you deal with EXISTS, you’ll find a JOIN will cost you a LOT less.

There are some gotchas to look out for with sub queries:

Your sub query can’t use an ORDER BY, unless it also uses a TOP statement too
Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query. If you’re going more than a few levels deep, you really should have someone check your logic. A JOIN may simplify your code greatly!
If a table appears only in a sub query and not in the outer query, then columns from that table cannot be included in the output (the select list of the outer query). Again, with a JOIN, you would have access to that table from the “outer” query.

In some Transact-SQL statements, the sub query can be evaluated as if it were an independent query. Conceptually, the sub query results are substituted into the outer query (although this is not necessarily how Microsoft SQL Server actually processes Transact-SQL statements with sub queries).

If you have any questions, please send them in. I’m here to help!

Leave a Reply Cancel reply