Using the Relational Data Framework › Use IN Predicates in WHERE Clauses › Comparing One or More Values

Comparing One or More Values

An IN predicate compares the column name to one or more strings or host variable names. Use this syntax to specify an IN predicate:

WHERE colname IN (string | :hostvar,...)

Although you could compare a column name to multiple values using multiple Boolean ORs, do this more efficiently by including an IN predicate in your WHERE clause. For example, these two SELECT statements are equivalent:

"SELECT CURRENT_STATE INTO :SYSTEMS_UP",
  "FROM SYSTEMS WHERE NAME IN ('CICS','IMS','VTAM')"

"SELECT CURRENT_STATE INTO :SYSTEMS_UP",
  "FROM SYSTEMS WHERE NAME = 'CICS'",
    "OR NAME = 'IMS' OR NAME = 'VTAM'"

When either statement executes, SQL selects the values in the CURRENT_STATE column from all rows where the value in the NAME column is CICS or IMS.

Note: The SYSTEMS table does not contain a row where the NAME column has the value VTAM, so SQL ignores this part of the WHERE clause.

An IN predicate is true only if the column value exactly matches any of the specified strings or host variable values. For example, suppose that you invoke this SQL statement:

"SELECT CURRENT_STATE INTO :RECOV_PROC",
  "FROM SYSTEMS WHERE RECOV_PROC IN ('FIX')"

When this statement executes, it selects no rows from the SYSTEMS table because the text string (FIX) does not exactly match any values in the column named RECOV_PROC. However, if you rewrite the statement as follows, the predicate is true because the string matches the column contents. In this case, SQL selects both values in the RECOV_PROC column.

"SELECT CURRENT_STATE INTO :RECOV_PROC",
  "FROM SYSTEMS WHERE RECOV_PROC IN ('FIXCICS','FIXIMS')"