Example of Correlated Subquery[Oracle]

Example of a correlated subquery

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)

Example of correlated subquery with individual components explained.

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.