SQL 2005+

SQL Server Subqueries

by Richard Carr, published at http://www.blackwasp.co.uk/SQLSubqueries.aspx

The thirtieth part of the SQL Server Programming Fundamentals tutorial examines subqueries. These can be nested queries allowing complex selections that are not possible using joins. They can also be used to update rows using the contents of other tables.

What are Subqueries?

A subquery is a query that appears within another statement. Generally this is a SELECT statement that is nested within another SELECT, although subqueries may also be used within commands that update data in a table. Usually a subquery returns a single value or one row of data, though there are situations where multiple rows are returned. This information can be used to set a variable's value, as a column in a query or within a WHERE clause of a containing statement.

Subqueries can be nested to several layers deep, with subqueries within other subqueries. This provides the means to generate very complex queries that solve complicated problems. This includes the creation of queries containing multiple tables that are not possible using standard joins. However, this benefit can be a drawback if the queries become too large to be easily understood. It can also lead to inefficient queries that are slow to execute and that should be separated into several smaller queries executed within a stored procedure.

Correlated Subqueries

A standard subquery usually generates a single value that is used in every row of the parent query's results. This allows SQL Server to optimise the statement so that the subquery is only executed once. The performance of such a query can then be comparable to the equivalent query using joins, where this is possible.

A correlated subquery may provide a different answer for each row of the parent query. This may be because it is being used to calculate a value that is based upon the contents of the other columns in each row of the parent query. Correlated subqueries can be very powerful but may exhibit lower performance because they are executed once for each row in the results.

Using Subqueries

In this article we will examine the use of standard and correlated subqueries by executing a series of example statements. Some of the queries are possible to recreate without the use of subqueries, using joins instead. In some cases, the equivalent query using joins would be more efficient. The samples are designed to demonstrate the syntax and use of subqueries. In a real-world scenario you should use judgement and performance testing to determine the most suitable style of query for the problem being addressed.

JoBS Database

The examples in this article use the JoBS database. This is the database that has been generated and populated with data throughout the course of this tutorial. If you do not have an up-to-date copy of the database you can create one using the script provided via the link at the top of this page.

Standard Subqueries

Let's start by running a simple query containing a standard subquery. The query below retrieves a list of all contracts within the JoBS database. The third column in the results is generated by the subquery, which retrieves the total value of all known contracts. This query allows the value of each contract to be compared to the total value. Note the requirement for the subquery to be contained within parentheses (). In this case a name has also been provided for the subquery's column.

SELECT
    ContractNumber,
    ContractValue,
    (SELECT sum(ContractValue) FROM Contracts) AS AllContractsTotal
FROM
    Contracts

In the above statement the query contains a standard subquery. The result of the subquery need only be retrieved once because it is the same for every row in the result set.

The resultant value of a subquery can be used in much the same way as any other value. For example, if we wished to determine the percentage value that each contract contributes towards the company's income, we can use the subquery within a calculation, as in the next query. Again, the result of the subquery does not vary from row to row, even though the result of the calculation does.

SELECT
    ContractNumber,
    ContractValue,
    100 * ContractValue / (SELECT sum(ContractValue) FROM Contracts) AS Percentage
FROM
    Contracts

Subqueries can be used in places other than the column list for a query. In the next example the subquery is used in the WHERE clause. In this case only contract rows that contribute at least 3% of the total contract income are retrieved:

SELECT
    ContractNumber,
    ContractValue,
    100 * ContractValue / (SELECT sum(ContractValue) FROM Contracts) AS Percentage
FROM
    Contracts
WHERE
    ContractValue / (SELECT sum(ContractValue) FROM Contracts) >= 0.03