Skip to main content

Celonis Product Documentation

IN
Description

IN returns true for all cases of a column or a value that match any of the values of the match list. An item in the match list can also be another column. Returns false otherwise. The reverse is true for NOT IN.

Syntax
 value/column IN ( value1/column1, ... )

 value/column NOT IN ( value1/column1, ... )
Supported types

Supported value types for the match list are STRING, INT, FLOAT, DATE, and any column of that type.

Usage

[NOT] IN can be used in the following contexts:

NULL handling

A match value can also be NULL. A NULL value matches with all NULL values in the match list. NULL values originating from columns in the match list are ignored.

Case sensitivity

Comparison of strings is case-sensitive

Use Cases
Examples

[1]

Column and match list contain only integers.

Query

Column1 

         CASE WHEN "Table1"."Column1" IN ( 1 , 3 ) THEN "Table1"."Column1" ELSE null END

Input

Output

Table1

Column1 : int

1

3

5

Result

Column1 : int

1

3

null

[2]

NOT IN: Results are negated. Column and match list contain only integers.

Query

Column1 

         CASE WHEN "Table1"."Column1" NOT IN ( 1 , 3 ) THEN "Table1"."Column1" ELSE null END

Input

Output

Table1

Column1 : int

1

3

5

Result

Column1 : int

null

null

5

[3]

Check if a value is contained in a column.

Query

Column1 

         CASE WHEN 1 IN ( "Table1"."Column1" ) THEN 'match' ELSE 'no match' END

Input

Output

Table1

Column1 : int

1

3

5

Result

Column1 : string

'match'

[4]

Column contains only integers and match list contains floats and integers. All cases of the column are matched.

Query

Column1 

         CASE WHEN "Table1"."Column1" IN ( 1.0 , 3 ) THEN "Table1"."Column1" ELSE null END

Input

Output

Table1

Column1 : int

1

3

Result

Column1 : int

1

3

[5]

Column and match list contain only dates.

Query

Column1 

         CASE WHEN "Table1"."Column1" IN ( {d '2000-01-02' } , {d '2001-12-31' } , {d '2004-06-15' } ) THEN "Table1"."Column1" ELSE NULL END

Input

Output

Table1

Column1 : date

Sun Jan 02 2000 00:00:00.000

Mon Dec 31 2001 00:00:00.000

Sun Apr 16 2006 00:00:00.000

Result

Column1 : date

Sun Jan 02 2000 00:00:00.000

Mon Dec 31 2001 00:00:00.000

null

[6]

Column and match list contain only strings and match list also contains empty string. Two cases of the column are matched.

Query

Column1 

         CASE WHEN ( "Table1"."Column1" IN ( '1' , '3' , '-10' , '' , 'aAa' , 'bbb' ) ) THEN "Table1"."Column1" ELSE null END

Input

Output

Table1

Column1 : string

'1'

'3'

'0'

'@'

'aAa'

'bBb'

Result

Column1 : string

'1'

'3'

null

null

'aAa'

null

[7]

Column and match list contain empty string. Empty string is matched.

Query

Column1 

         CASE WHEN ( "Table1"."Column1" IN ( '1' , '3' , '-10' , '' ) ) THEN "Table1"."Column1" ELSE null END

Input

Output

Table1

Column1 : string

''

'2'

null

'3'

'4'

Result

Column1 : string

''

null

null

'3'

null

[8]

Column and match list contains a NULL value and strings. The IN operator is used for column filtering.

Query

Filter 

         FILTER "Table1"."Column1" IN ( NULL , '3' );

Column1 

         "Table1"."Column1"

Input

Output

Table1

Column1 : string

null

''

'3'

'4'

Result

Column1 : string

null

'3'

[9]

Column contains a NULL value and 3 strings and match list 3 integers. The IN operator is applied on the COUNT aggregation of the column. One value of the match list is matched.

Query

Column1 

         CASE WHEN COUNT ( "Table1"."Column1" ) IN ( 1 , 2 , 3 ) THEN 1 ELSE 0 END

Input

Output

Table1

Column1 : string

null

'1'

'2'

'3'

Result

Column1 : int

1

[10]

IN with another column in the match list.

Query

Column1 

         CASE WHEN "Table1"."Column1" IN ( "InTable"."Column1" ) THEN 1 ELSE 0 END

Input

Output

InTable

Column1 : int

1

2

null

Table1

Column1 : int

2

3

4

Result

Column1 : int

1

0

0

[11]

IN with another column containing NULL in the match list (in CASE WHEN statement).

Query

Column1 

         CASE WHEN "Table1"."Column1" IN ( "InTable"."Column1" ) THEN 'match' ELSE 'no match' END

Input

Output

InTable

Column1 : int

2

3

null

Table1

Column1 : int

1

2

null

Result

Column1 : string

'no match'

'match'

'no match'

See also: