Types of Subqueries

There are two basic types of subqueries:

• Single row subqueries    Return zero or one row to the outer SQL statement.

• Multiple row subqueries    Return one or more rows to the outer SQL statement.

In addition, there are three subtypes of subqueries that may return single or multiple rows:

• Multiple column subqueries    Return more than one column to the outer SQL statement.

• Correlated subqueries    Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL statement.

• Nested subqueries    Are placed within another subquery. You can nest subqueries to a depth of 255.

You ll learn about each of these types of subqueries in this chapter, and see how to add subqueries to SELECT , UPDATE , and DELETE statements. Let s plunge in and look at how to write single row subqueries.