Describe and Write Subqueries in SQL

As the name implies, a subquery^[1] is actually a query within a query. Subqueries are sometimes referred to as nested queries. In SQL Server 2000, there is no limit to the number of subqueries that a query can contain.

A subquery can sometimes be used in place of joining two tables together. Generally, there is no performance difference between using joins with multiple tables or using subqueries, with one exception. If the subquery contains an EXISTS clause, it would be preferable to construct a join.

Subqueries are generally used as part of a Transact-SQL statement with the IN keyword, using this syntax: A subquery is used to specify either a value (the scalar subquery, which returns one value), a row (the row subquery, which returns one row), or a Table (the Table subquery, which returns a result Table).

SELECT column(s)
FROM tables(s)
WHERE column IN (subquery)

An IN keyword in the WHERE clause restricts the results of a query to values that match a list of subsequent values. In this example, the subquery will produce a list of values that match the values in the column that is queried in the where clause.
You can use a subquery anywhere that an expression is required within a SELECT, UPDATE, INSERT, and DELETE Transact-SQL statement, as long as it does not appear in the ORDER BY clause of each of these statements.

SELECT Salary
FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID 
FROM ActiveEmployee)

In the example above, only salaries for employees who are in the ActiveEmployee table are retrieved. This is because SQL Server 7 does the innermost query first (the one from the ActiveEmployee table). This builds a resultset of employees. Then the salaries are selected from the Employees table only where the EmployeeID is equal to the employees that are in the prior resultset. In the next lesson, you will learn how to construct distributed select statements to retrieve data across multiple servers.

Click the exercise link below to practice creating a subquery.
Using Subqueries - Exercise

[1]Subquery: A query within a query.