Subqueries typically have syntactical autonomy, meaning that they can be executed on their own. However, there are certain subqueries that are not independent from their parent queries; rather, they are correlated to the larger parent query in a manner that intertwines the two.
In SQL, a correlated subquery is one that is integrated with its parent query, incorporating references to elements of the parent query, and thus it does not exist as a stand-alone query. Let's take a look at an example using the pokemon table. As you can see in the description below, the table contains information about Pokémon types and base stats.
Now, suppose that you want to create a single query that lists all Pokémon whose attack is greater than the average for its type. Using subquery, this can be solved as follows:
In the query shown above, subquery calculates the average attack for Pokémon with the same type1 and type2 values as the parent Pokémon in the outer query. We say the subquery is correlated to its parent as it refers columns from parent query with alias. The outer query then selects all columns from the pokemon table where the attack is greater than the calculated average.
In this example, the subquery is not executing as a stand-alone query and then passing back its result like noncorrelated subqueries do. Instead, the correlated subquery is executing once for each value that the parent query finds for each row, passing the value for the type1 and type2 into the subquery, and determining the average attack for the particular pokemon type. Finally, it uses the result of that query in line 4 to determine whether the row in the parent query is greater than the average or not.
You can validate the results with:
Correlated subqueries can exist in SELECT, UPDATE, and DELETE statements. They are "correlated" by way of a column reference from the parent query within the subquery. Note that a table alias is not required in the subquery if no column name conflict exists. We could have just referenced any column from the parent query, and as long as there isn't an identically named column in the subquery. However, as we've seen in the Pokémon example above, if there is any column name conflict, table alias is required.
It is also worth noting that correlated subqueries may introduce some performance degradation into a query. The process of correlating rows from one or more subqueries with the outer, or parent, query or queries may consume a significant amount of processing time. However, often a correlated subquery is the only solution that can accomplish certain tasks.
0 Comments