Subquery that follows a comparison operator

A subquery that follows a comparison operator (=, >, <, >=, <=, !=, <>, !>, !<) is called a comparison. The optimizer converts these subqueries to joins if the subquery:

  • returns exactly one value for each row of the main query.

  • does not contain a GROUP BY clause

  • does not contain the keyword DISTINCT

  • is not a UNION query

  • is not an aggregate query

 Example