Join Queries  «Prev 

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.