| Lesson 10 || Using subqueries |
| Objective ||Describe and Write Subqueries in SQL |
Describe and Write Subqueries in SQL
As the name implies, a subquery
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.
When to use subqueries
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).
WHERE column IN (subquery)
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
Transact-SQL statement, as long as it does not appear in the
ORDER BY clause of each of these statements.
An example of a subquery would be this Transact-SQL statement:
WHERE EmployeeID IN (SELECT EmployeeID
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.
Using Subqueries - Exercise
A query within a query.