Using correlated sub-queries in SQL Server

2 views

Skip to first unread message

ragav...@gmail.com

unread,

Aug 28, 2006, 4:24:05 AM8/28/06

to SQL Developers

***********
sub-query
***********

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.

************************
correlated sub-query
************************

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