Using correlated sub-queries in SQL Server

by Tim Chapman | Jul 31, 2006 6:35:00 PM

Tags: Databases, Microsoft SQL Server, server, Tim Chapman, sub-query, correlated sub-query, SQL Server Newsletter

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

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.

Print/View all Posts Comments on this article

Listings A & B?emanuel@...  | 10/17/06

The links workAndrewNKeller  | 10/19/06

The linksmdhealy@...  | 06/28/07

Self Join?keith_howes@...  | 10/24/06

RE: Using correlated sub-queries in SQL Serveroscco@...  | 06/28/07

RE: Using correlated sub-queries in SQL Serveroscco@...  | 06/28/07