Subqueries and Nested SELECTs
Learn how to use different types of subqueries to make SQL queries smarter and more dynamic.
We'll cover the following...
Imagine that our online store intends to reward its top customers.
But how should we define a top customer? One possible definition is a customer whose individual order value exceeds the average value of all orders placed in the store. To determine this, we must first calculate the average order value and then identify the customers whose orders surpass that average.
This two-step reasoning exemplifies the precise scenario in which subqueries demonstrate their effectiveness. In this lesson, we will examine how to nest queries within one another to address complex analytical problems in a clear and efficient manner.
By the end of this lesson, we will be able to:
Understand the concept of a subquery and its purpose.
Differentiate between scalar, multi-row, and correlated subqueries.
Use subqueries with operators like
IN,NOT IN,ANY, andALL.Apply the
EXISTSoperator to check for the existence of rows.
Place subqueries in the WHERE, FROM, and SELECT clauses of a query.
Subquery
At its core, a subquery, also known as a nested or inner query, is simply a SELECT statement that is placed inside another SQL statement. The outer statement is referred to as the outer query, and it utilizes the result of the subquery to determine its own output. Think of it as a helper query that runs first to provide a value or a list of values that the main query needs to complete its job.
The primary reason we need subqueries is to solve problems that require multiple steps of data retrieval. Instead of running one query, noting down the result, and then manually plugging that result into a second query, a subquery lets us automate this entire process in a single, elegant statement.
The general syntax looks like ...