When subqueries are used with quantifiers and comparison operations or IN/NOT IN predicates, the results are determined as follows.

IF this quantifier is specified … | AND the predicate is … | THEN the result is … | WHEN … |
---|---|---|---|

ALL | a comparison operation | TRUE | the comparison of expression and every value in the set of values returned by subquery produces true results. |

IN | TRUE | expression is equal to every value in the set of values returned by subquery. | |

NOT IN | TRUE | expression is not equal to any value in the set of values returned by subquery. | |

ALL | a comparison operation | TRUE | subquery returns no values. |

IN | |||

NOT IN | |||

ANY SOME |
a comparison operation | TRUE | the comparison of expression and at least one value in the set of values returned by subquery is true. |

IN | TRUE | expression is equal to at least one value in the set of values returned by subquery. | |

NOT IN | TRUE | expression is not equal to at least one value in the set of values returned by subquery. | |

a comparison operation | FALSE | subquery returns no values. | |

IN | |||

NOT IN |