SQL recognizes the quantifiers ANY (or SOME) and ALL. A quantifier allows one or more expressions to be compared with one or more values such as shown by the following generic example.

IF you specify this quantifier … | THEN the search condition is satisfied if expression LIKE pattern_string … is true for … |
---|---|

ALL | every string in the list. |

ANY | any string in the list. |

The ALL quantifier is the logical statement FOR .

The ANY quantifier is the logical statement FOR Ǝ.

The following table restates this.

THIS expression … | IS equivalent to this expression … |
---|---|

x LIKE ALL ('A%','%B','%C%') | x LIKE 'A%' AND x LIKE '%B' AND x LIKE '%C%' |

x LIKE ANY ('A%','%B','%C%') | x LIKE 'A%' OR x LIKE '%B' OR x LIKE '%C%' |

The following statement selects from the employee table the row of any employee whose job title includes the characters “Pres” or begins with the characters “Man”:

SELECT * FROM Employee WHERE JobTitle LIKE ANY ('%Pres%', 'Man%');

The result of this statement is:

EmpNo | Name | DeptNo | JobTitle | Salary |
---|---|---|---|---|

10021 | Smith T | 700 | Manager | 45, 000.00 |

10008 | Phan A | 300 | Vice Pres | 55, 000.00 |

10007 | Aguilar J | 600 | Manager | 45, 000.00 |

10018 | Russell S | 300 | President | 65, 000.00 |

10012 | Watson L | 500 | Vice Pres | 56, 000.00 |

For the following forms, if you specify the ALL or ANY/SOME quantifier, then the subquery may return none, one, or several rows.

If, however, a quantifier is not used, then the subquery must return either no value or a single value as described in the following table.

This expression … | Is TRUE when expression matches … |
---|---|

expression LIKE (subquery) | the single value returned by subquery. |

expression LIKE ANY (subquery) | at least one value of the set of values returned by subquery; is false if subquery returns no values. |

expression LIKE ALL (subquery) | each individual value in the set of values returned by subquery, and is true if subquery returns no values. |