Logic Circuit
Subqueries and derived tables can add great versatility to SQL statements, cut down complexity, but can occasionally be a curse when their effect on performance is poorly understood. Surely everyone understands the various types of subqueries and how they are used? If you felt a twinge of doubt, here is Rob Sheldon's easy guide to the subject.
ew elements within a Transact-SQL statement are as versatile as the subquery. A subquery—also referred to as an inner query or inner select—is a SELECT statement embedded within a data manipulation language (DML) statement or nested within another subquery. You can use subqueries in SELECT, INSERT, UPDATE, and DELETE statements wherever expressions are allowed. For instance, you can use a subquery as one of the column expressions in a SELECT list or as a table expression in the FROM clause.
A DML statement that includes a subquery is referred to as the outer query. The following guidelines provide details about how to implement subqueries in your outer queries or in other subqueries:
You must enclose a subquery in parenthesis.
A subquery must include a SELECT clause and a FROM clause.
A subquery can include optional WHERE, GROUP BY, and HAVING clauses.
A subquery cannot include COMPUTE or FOR BROWSE clauses.
You can include an ORDER BY clause only when a TOP clause is included.
You can nest subqueries up to 32 levels.
There are several ways you can categorize subqueries—by the number of results they returns, whether they’re correlated (linked to the outer query), or where they’re used within a DML statement. For the purposes of this article, I take the last approach and explain how subqueries can be implemented in the SELECT, FROM, and WHERE clauses of a SELECT statement. Although you can implement subqueries in other clauses and other statement types, the examples I provide should demonstrate the essential principles of how subqueries can be used in any circumstances. (The examples all return data from the
ew elements within a Transact-SQL statement are as versatile as the subquery. A subquery—also referred to as an inner query or inner select—is a SELECT statement embedded within a data manipulation language (DML) statement or nested within another subquery. You can use subqueries in SELECT, INSERT, UPDATE, and DELETE statements wherever expressions are allowed. For instance, you can use a subquery as one of the column expressions in a SELECT list or as a table expression in the FROM clause.
A DML statement that includes a subquery is referred to as the outer query. The following guidelines provide details about how to implement subqueries in your outer queries or in other subqueries:
You must enclose a subquery in parenthesis.
A subquery must include a SELECT clause and a FROM clause.
A subquery can include optional WHERE, GROUP BY, and HAVING clauses.
A subquery cannot include COMPUTE or FOR BROWSE clauses.
You can include an ORDER BY clause only when a TOP clause is included.
You can nest subqueries up to 32 levels.
There are several ways you can categorize subqueries—by the number of results they returns, whether they’re correlated (linked to the outer query), or where they’re used within a DML statement. For the purposes of this article, I take the last approach and explain how subqueries can be implemented in the SELECT, FROM, and WHERE clauses of a SELECT statement. Although you can implement subqueries in other clauses and other statement types, the examples I provide should demonstrate the essential principles of how subqueries can be used in any circumstances. (The examples all return data from the