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.