Identify the Connection between a Query and a Subquery.
Identify Connection between Query and Subquery
You have seen plenty of subqueries in this module so far.
Now you will learn about an advanced form of subquery called the correlated subquery.
A correlated subquery is a subquery in which part of the query refers back to data in the outer query.
The advantage of using a correlated subquery is that you can write a query that isolates the subquery portion while still combining the subquery with the main query as if it were a join. This form of query is sometimes easier to understand than a complicated multi-table join.
Correlated subquery syntax
Here is the general syntax:
SELECT col1, col2, ...FROM table alias1WHERE col3 =
(SELECT col4 FROM table alias2
WHERE alias1.col5 = alias2.col6)
Notice that alias1 (the alias for the table in the outer query) appears in the WHERE clause of the subquery. This connection between a column FROM the outer query and a column FROM the subquery makes this a correlated subquery.
Here is an example, using the Pet Store schema. The query in the following MouseOver answers the question: Which PRODUCT record has been updated after the last time an entry was added to the Pet Care Log for that PRODUCT?