Identify the Connection between a Query and a Subquery.
Identify the Connection between a Query and a 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?