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[1].
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?
Use an alias for your table name so that you can easily refer to the table in the subquery. Here, the letter P is the alias for the PRODUCT table.
In this example, the greater than symbol (>) is the operator that compares the outer query data (LAST_UPDATE_DATE) with the subquery data (MAX(LOG_DATETIME)). You can also use other operators such as equal, IN, LIKE, or NOT IN.
The subquery uses the PET_CARE_LOG table and assigns PCL as the alias for this table. This makes it more convenient to write the WHERE clause.
Here is the comparison that makes this a correlated subquery. The data FROM the outer query is used as criteria for the subquery. In this example, the criteria restricts the rows in the subquery to those rows with a PRODUCT_ID that matches the PRODUCT_ID of the current row of the outer query.
SELECT PRODUCT_NAME
FROM PRODUCT P
WHERE LAST_UPDATE_DATE >
(SELECT MAX(LOG_DATETIME)
FROM PET_CARE_LOG PCL
WHERE P.PRODUCT_ID=PCL.PRODUCT_ID)
Use an alias for your table name so that you can easily refer to the table in the subquery. Here, the letter P is the alias for the PRODUCT table.
In the next lesson, you will learn how to combine the correlated subquery with another command to create more efficient queries.
[1]Correlated subquery: A subquery in which part of the subquery refers back to data in the outer query. Correlated subqueries are a part of standard SQL.