Wrap-Up b4 In

• Return a single column and row, a single column with multiple rows, and multiple columns and rows
• Are independent of the containing statement (noncorrelated subqueries)
• Reference one or more columns from the containing statement (correlated subqueries)
• Are used in conditions that utilize comparison operators as well as the specialpurpose operators in, not in, exists, and not exists
• Can be found in select, update, delete, and insert statements
• Generate result sets that can be joined to other tables (or subqueries) in a query
• Can be used to generate values to populate a table or to populate columns in a query’s result set
• Are used in the select, from, where, having, and order by clauses of queries

What Is a Subquery?

A subquery is a query contained within another SQL statement, or a containing statement.

• Always enclosed in parentheses.
• Executed prior to the containing statement.
• The data returned by any subqueries is discarded after the containing statement is finished.

select first_name, last_name, customer_id
from customer
where customer_id = **(select max(customer_id) from customer)**;

Types

Differences by type of result set:

• A single row with a single column - scalar subquery.
• Multiple rows with a single column.