SQL 2005+

SQL Server Subqueries

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.