How to use a dependent subquery in SQL Server

A subquery is the embedding of a SQL Sever statement into another SQL Sever statement. The database engine performs a query operation on a subquery as a virtual table. Subqueries can be used as a table in a join statement, either as a value in a SELECT statement, as a SQL sever query clause, or as a phrase in a SQL sever query clause, mixed with a data manipulation statement.

The execution of a subquery relies on nested queries. The query tree starts from the innermost layer and executes one level out. High-level nested queries can access the results of low-level nested queries.

What is a dependent subquery?

Unlike classic subqueries, dependent subqueries depend on external queries. External queries and subqueries are related, especially in the where statement of the subquery. Related subqueries work by executing an external query when a reference to an external query is found in a subquery, and the result is returned to the subquery. The subquery operation is then performed on the result set returned by the external query.

Execution performance of correlated subqueries

Because the subquery in the correlated subquery executes on the result set returned by the external query, the efficiency of the query is definitely reduced. The performance of a subquery depends entirely on the query and related data. However, if the statements of related subqueries are written efficiently, they perform better than those that use several connections and temporary tables.

An example

One of the main advantages of correlated subqueries is that it can accomplish different problems in traditional SQL sever queries. For example, you can easily accomplish such a task by using related subqueries: To get a sequential sales total result or to sell the most items in each state.

The following example shows how to use a related subquery to create a process that runs a total. First, run the following script to create a sample table:

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

Now that the table holds these data fields, let's run the script to add some records to it. Listing A shows a program of 300 records entered into a table, where saleprice some changes. Although Saleprice has not changed much, it is enough to explain how the associated subquery works. Next, run the related subquery in Listing B to create the sales report.

A sequential sales totals query that is introduced into a report is a related subquery. For each product in the table, the dependent subquery restates its result set and adds and operates the saleprice of each product sold before returning the result to the result set.

Give it a try.

Try running the above example and playing with the code. For me, I can learn a lot of new technologies from examples. If you've mastered the principles of related subqueries, consider getting yourself into the top echelon of the database developer.

Tim Chapman, a SQL Server database administrator, now works for a bank in Louisville,ky, who has over 7 years of it work experience.