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 - operand IN ( ... )

Other versions: 7.31 | 7.40 | 7.54

Syntax

... operand [NOT] IN { (operand1[, operand2[, ...]]) } 

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

Variants

1. ... operand [NOT] IN (operand1[, operand2[, ...]])

2. ... operand [NOT] IN ( SELECT subquery_clauses [UNION ...] )

Effect

Checks whether the value of an operand operand matches a value in a set of values specified in parentheses. The set of values can be specified as follows:

• Using a comma-separated list of individual literals or host variables.

• Using a scalar subquery.

The following applies to operand:

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

• Aggregate expressions can also be used in a HAVING clause.

Variant 1

... operand [NOT] IN (operand1[, operand2[, ...]])

Effect

This relational expression is true if the value of the operand operand matches (does not match) the content of one of the operands operand1, operand2 ... in a parenthesized comma-separated value list.

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

• Literals and host variables can be specified.

• The content must match the data type of operand in accordance with the rules for lossless assignments. This is checked by the strict modes of the syntax check from Release 7.40, SP08 and can produce a syntax error or raise an exception .

It is also possible to specify just one operand operand1 in the parentheses. Blanks can be placed after the opening parenthesis, in front of the closing parenthesis, and after the commas. If blank is placed after the opening parenthesis, there must also be a blank in front of the closing parenthesis.

Note

No columns col, no host expressions, and no other SQL expressions can be specified here on the right side. SQL expressions are, however, specified in the variant of IN with an operand list.

Example

Reads the bookings in which the class is not in the value range (C - Business, F - First, Y - Economy) of the corresponding domain in ABAP Dictionary.

SELECT * 
       FROM sbook 
       WHERE class NOT IN ('C','F','Y') 
       INTO TABLE @DATA(sbook_tab). 

IF sy-subrc = 0. 
  "Error handling 
ENDIF. 

Variant 2

... operand [NOT] IN ( SELECT subquery_clauses [UNION ...] )

Effect

This relational expression is true if the value of the operand operand is (is not) contained in the results set of a subquery. The clauses in the subquery subquery_clauses must represent a scalar subquery. The language element UNION can be used to combine the results sets of multiple subqueries. In this case, special rules query_clauses apply for specifying clauses.

Example

Reads the geographical latitude and longitude of a city from the database table SGEOCITY, where this city is the city of origin of a flight in the database table SPFLI.

DATA: carr_id TYPE spfli-carrid VALUE 'LH', 
      conn_id TYPE spfli-connid VALUE '400'. 


SELECT SINGLE city, latitude, longitude 
       FROM sgeocity 
       WHERE city IN ( SELECT cityfrom 
                             FROM spfli 
                             WHERE carrid = @carr_id AND 
                                   connid = @conn_id ) 
       INTO (@DATA(city), @DATA(lati), @DATA(longi)).