Effective SQL: 61 Specific Ways to Write Better SQL, First Edition

6. Subqueries

A subquery is a table expression created by embedding a complete SELECT statement inside parentheses and giving it a name. In general, you can use a subquery anywhere you can use a table name. As you will learn in this chapter, you can also use a subquery that returns a single column wherever you can use a list of values—for example, in an IN clause. A subquery that returns one column and zero or only one value can be used anywhere you can use a column name or a single literal. The subquery is a powerful construct that gives you lots of additional flexibility in SQL. The first item in this chapter explores in depth where you can use the different kinds of subqueries.

Item 40: Know Where You Can Use Subqueries

We use the term