How to Write Subqueries (Simple and Correlated)

A subquery is select statement contained within an outer select statement. Two types of subqueries are available in SQL: the simple subquery and the correlated subquery. In this topic you will learn how to write each type of subquery.

To learn how write simple and correlated subqueries, follow these steps:

1. You'll need to setup the MySQL database tables. The instructions for the setup can be found in How to add comments in simple SQL selects. Follow steps 1 through 7 before proceeding to the next step.
2. Let's write a couple of subqueries now.
3. You will write a select statement that selects city names that have a population greater than the average city population. Execute the following statement: This is an example of a simple subquery. Note that the simple subquery appears in the where clause and is enclosed in parentheses.
4. Next, you will write a select statement that lists state names (stored in the States table) of states that don't appear in the Cities table. Execute the following statement: This is an example of a correlated subquery. Like the simple subquery, the correlated appears in the where clause and is enclosed in parentheses. But this query has a correlation, or connection, to the outer query (select). The states reference that I've highlighted with the arrow above ensures that the row retrieved in the subquery has the same state abbreviation as the row in the outer query. If the row exists for this state in the Cities table, this state is excluded from the output because we use the not keyword prior to the exists keyword. You may wonder why I select "0" in the subquery. The reason is no data is required from the inner select and so I use "0" as a place holder. Any numeric (or character) literal value will suffice.