How to Write Subqueries (Simple and Correlated)
See SQL: Tips and Tricks for similar articles.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:
- 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.
- Let's write a couple of subqueries now.
- 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.
- 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 thenot
keyword prior to theexists
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.
Related Articles
- Reset Root Password in MySQL on Windows
- How to Create an ER Diagram for a MySQL Database with Free Tools
- How to Round Up in SQL
- How to Concatenate Strings in SQL
- How to Select All Columns in a Row in SQL
- How to Check Multiple Conditions in SQL
- How Work with White Space and Semicolons in Simple SQL Selects
- How to Sort Records in SQL
- How to Write Subqueries (Simple and Correlated) (this article)