ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Operands and Expressions →  ABAP SQL - SQL Conditions sql_cond →  sql_cond - rel_exp for Statements →  sql_cond - IN ( ... ) 

sql_cond - ( operand1, operand2, ... ) IN ( ... )

Other versions: 7.31 | 7.40 | 7.54

Syntax

... ( operand1, operand2[, ...] ) IN { ( ( operand11, operand21[, ...] )[, 

                 
                 
     ( operand12, operand22[, ...] )] ) } 

                 
                 | { ( SELECT
subquery_clauses [UNION ...] ) } ... 

Variants

1. ... ( operand1, operand2[, ...] ) IN ( ( operand11, operand21[, ...] )[, ...] ) )

2. ... ( operand1, operand2[, ...] ) IN ( SELECT subquery_clauses [UNION ...] )

Effect

Checks whether the values of the operands operand1, operand2, ... specified as a parenthesized comma-separated list match a value tuple from a set of value tuples specified in parentheses. The set of value tuples can be specified as follows:

• Using a comma-separated list of parenthesized tuples ( operand11, operand21[, ...] ), ( operand12, operand22[, ...] ), ...

• Using a subquery (not yet implemented)

The following applies to operand1, operand2, ...:

• Any SQL expressions except aggregate expressions and window expressions can be specified.

• Aggregate expressions can be used in a HAVING clause.

At least one blank must be placed after every opening parenthesis and before every closing parenthesis. The comma-separated list operand1, operand2, ... must contain at least two elements.

Note

The operator NOT is not currently supported.

Variant 1

... ( operand1, operand2[, ...] ) IN ( ( operand11, operand21[, ...] )[, ...] ) )

Effect

This relational expression is true if the value of every operand operand1, operand2, ... on the left side matches the content of the operand of a parenthesized value tuple ( operand11, operand21[, ...] ) in the same place on the right side. Every value tuple must contain the same number of elements as in the comma-separated list operand1, operand2, ... on the left side. The value tuples themselves are specified as a parenthesized comma-separated list (which can also consist of only one element).

operand11, operand21, ... of a value tuple can be any SQL expressions except window expressions. Aggregate expressions are currently only possible in a HAVING clause. Literals, host variables, and host expressions are handled like elementary SQL expressions, which means they are mapped to dictionary types instead of being converted.

Notes

• This variant of IN can be replicated by individual comparisons joined using AND and OR and the relational operator =, but this method does not permit SQL expressions on the right side. This is also how this variant of IN is distinguished from the variant with a single operand.

• When this variant of IN is used, ABAP SQL bypasses table buffering.

• When used, this variant of IN, applies the strict mode from Release 7.54.

Example

The query finds all rows in which the values of the three columns on the left side match one of the two value tuples on the right side.

DATA cityfrom TYPE spfli-cityfrom VALUE 'Frankfurt'. 
DATA cityto   TYPE spfli-cityto   VALUE 'New York'. 
cl_demo_input=>new( 
  )->add_field( CHANGING field = cityfrom 
  )->add_field( CHANGING field = cityto 
  )->request( ). 
cityfrom =  to_upper( cityfrom ). 
cityto   =  to_upper( cityto ). 

SELECT * 
       FROM spfli 
       WHERE ( carrid, cityfrom, cityto ) 
         IN ( ( 'LH', @cityfrom, @cityto ), 
             ( 'UA', @cityfrom, @cityto ) ) 
       INTO TABLE @DATA(itab). 

cl_demo_output=>display( itab ). 

Variant 2

... ( operand1, operand2[, ...] ) IN ( SELECT subquery_clauses [UNION ...] )

Effect

This variant is not yet implemented.