Data Management

Using correlated sub-queries in SQL Server

Possessing the ability to use a correlated sub-query will allow you to solve problems that other database developers cannot. Tim Chapman discusses how to use this extremely useful programming construct.

A sub-query is a SQL Server statement embedded inside of another SQL Server statement. The database engine treats a sub-query as a virtual table for the execution of the query. A sub-query can be used as a table in a join statement, as a single value in a select statement, in the where clause of a SQL Server query, in the having clause of a SQL Server query, or incorporated in data manipulation statements.

Sub-query execution is dependent upon the nesting level of the query. The execution tree goes from inner-most queries to outer-most queries. The higher nested queries can access the results returned by the lower nested queries.

What is a correlated sub-query?

Unlike a typical sub-query, a correlated sub-query is dependent upon the outer query. The outer query and the sub-query are related typically through a WHERE statement located in the sub-query. The way a correlated sub-query works is when a reference to the outer query is found in the sub-query, the outer query will be executed and the results returned to the sub-query. The sub-query is executed for every row that is selected by the outer query.

Weekly SQL tips in your inbox
TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

Performance implications of correlated sub-queries

Due to the fact that the sub-query in a correlated sub-query can be executed for every row returned in the outer query, performance can be degraded. With a sub-query, performance is totally dependent upon the query and the data involved. However, if written efficiently, a correlated sub-query will outperform applications that use several joins and temporary tables.

An example report

The main advantage of a correlated sub-query is that you can use it to solve problems that cannot be solved with a conventional SQL Server query. Results such as running total sales columns or the highest selling product in each state can easily be accomplished with the use of a correlated sub-query.

Here's an example of how you can use a correlated sub-query to create running aggregated totals in a query. First, run the script below to create the table you'll use to run your report.

CREATE TABLE SalesHistory
(
      SaleID INT IDENTITY(1,1),
Product VARCHAR(30),
      SaleDate SMALLDATETIME,
      SalePrice MONEY
)

Now that there is a table to hold the data, lets run a script to add some records to our table. View Listing A, which enters 300 records into the table with some variations in the SalePrice. The variations in the SalePrice field will be slight, but they should be enough to clearly show how the correlated sub-query works. Now run the correlated sub-query in Listing B to generate the sales report.

The running total query produced by the report is the correlated sub-query. For each product in the table, the correlated sub-query iterates the resultset and sums the SalePrice for every product sold before that record in the resultset.

Give it a try

Try the example above and play around with the code. For me, having examples are a great way to learn new technology.  If you are able to master the concept of correlated sub-queries, then you can consider yourself in the upper echelon of database developers.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

9 comments
russtye
russtye

The links for listing A and listing B do not work.

aravindsamy
aravindsamy

the subquery is executed for every row that is selected by an outer query

ahmedhinnawi
ahmedhinnawi

I am seeking a short t-sql code to calculate the running sum..

oscco
oscco

Found the windows with the code. Small windows displayed in corner of 2nd monitor. The confusing point is that another full web page (this one) opens up ALONG WITH the new window containing the code (which may appear on another monitor or in the corner where you are not expecting it.) Hope this helps if you seem to have trouble opening the listings.

oscco
oscco

Links are NOT working. If you hover over listings A and B, you will see the actual page pointed to is the page we are on. I have all popup blocking disabled and no dice. Would like to see your listings.

keith_howes
keith_howes

Isn't this idea the same as a self join? For instance: Select s1.Product , s1.SaleID , s2.RunningTotal From saleHistory s1 INNER JOIN ( Select Sum(SalePrice) as runningTotal From SaleHistory ) s2 ON s1.product= s2.product WHERE s2.saleid< s1.saleid Group By s1.Product, s1.SaleID

emanuel
emanuel

The article references listings A & B, but the links seems broken.

AndrewNKeller
AndrewNKeller

You are probably blocking pop-ups, though. Hold down the CTRL key when you click on the "Listing A" or "Listing B" link, and they should show up just fine. --Andrew

Editor's Picks