SOME

Use the SOME keyword with comparison operators to permit a query to return a set of values rather than a single value. You can use SOME with the following comparison operators:

     =   ¬=   >   >=   <   <=   < >

The symbol ¬= is an alternative symbol for < > (not equal to). It is an ANSI SQL operator. If you are using remote data access, the preferred symbol is < >.

ALL, ANY, and IN can also be used to return a set of values:

• When ALL is used, all values in the set returned are satisfied.
• When ANY or SOME is used, at least one value in the set returned is satisfied.
• IN can be used in a subquery in place of either = SOME or = ANY.

The following query produces a list of employees who work in the Eastern division. First, the subquery finds the department numbers in the Eastern division. Then, the main query finds the employees who work in these departments.

SELECT NAME, ID
FROM Q.STAFF
WHERE DEPT = SOME
   (SELECT DEPTNUMB FROM Q.ORG WHERE DIVISION='EASTERN')

The keyword SOME is used in this query because there are multiple departments in the Eastern Division. If ALL is used instead of SOME (or ANY), the result is an empty set. No employee works in all the departments of the Eastern division.