Inside Microsoft® SQL Server® 2008: T-SQL Querying

Chapter 6. Subqueries, Table Expressions, and Ranking Functions

This chapter covers subqueries, which are queries within queries, and ranking calculations. Subqueries can be scalar, multivalued, or table valued. You can use a scalar subquery where a single value is expected. For example, the following query returns the order with the maximum order ID:

USE InsideTSQL2008;

SELECT orderid, custid
FROM Sales.Orders
WHERE orderid = (SELECT MAX(orderid) FROM Sales.Orders);

The scalar subquery in bold is in charge of returning the maximum order ID. This subquery is self-contained, meaning that it has no dependency on the outer query.

A subquery that has a dependency on the outer query is known as a correlated subquery. For example, the following query returns ...