• google plus

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:
    Simple subquery to display cities with population greater than average
    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:
    Simple subquery to display cities with population greater than average
    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.
Author: Stephen Withrow

Stephen has over 30 years' experience in training, development, and consulting in a variety of technology areas including Java, C, C++, XML, JavaScript, AJAX, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen is a published writer in both technical and non-technical endeavors. Stephen received an undergraduate degree in Computer Science and Physics from Florida State University.

About Webucator

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, XML, Windows, Java, Adobe, HTML5, JavaScript, Angular, and much more. Check out our complete course catalog.