602SQL Documentation Index  
<­BETWEEN Predicate UpConditions and Predicates EXISTS Predicate­>

IN Predicate

IN_predicate ::= expression1 [NOT] IN {( expression { , expression } ) | ( query_expression )}

The IN predicate indicates whether the value of expression1 is among the values of the specified expressions or among the values of the query_expression (subquery) result.

The query_expression result must be scalar (must contain only one column). The subquery result type and types of all expressions must be comparable with the expression1 type.

Example (value count):

Select invoices from odd months only.

SELECT *
FROM Invoices
WHERE Month(date1) IN (1,3,5,7,9,11)

Example (subquery):

Select invoices from companies in New York.

SELECT Invoices.number,Invoices.company
FROM Invoices
WHERE company IN (SELECT number
                FROM Companies
                WHERE city LIKE 'New York%')

The same result can be obtained using a JOIN.

SELECT DISTINCT Invoices.number, Invoices.company
FROM Invoices, Companies
WHERE Companies.number=Invoices.company
 AND (Companies.city.='New York')
<­BETWEEN Predicate UpConditions and Predicates EXISTS Predicate­>