Skip to main content

Search

Welcome to Tibero GTS!

Maximum Number of Conditions When Using the IN Clause

Document Type | Technical Information

Category | APP Development

Applicable Product Version | T6, T7

Document Number | TDETI012

 

Overview

In Tibero, the IN clause can be used without any specific restriction on the number of conditions.

 

Method

1. Composite comparison available 

<Example>
SQL> CREATE TABLE TEST (NO NUMBER, NAME VARCHAR(10));
SQL> INSERT INTO TEST 
     SELECT LEVEL, 'NAME'||LEVEL FROM DUAL CONNECT BY LEVEL < 100001;
SQL> COMMIT;
SQL> SELECT * FROM TEST WHERE NO IN (1,2,...omitted,29999,30000);
        NO NAME       
---------- ----------
        1 NAME1      
        2 NAME2      
    ...omitted
    29999 NAME29999
    30000 NAME30000  
SQL> SELECT * FROM TEST WHERE (NO,NAME) IN ((1,'NAME1'),(2,'NAME2'),
     ...omitted
     (29999,'NAME29999'),(30000,'NAME30000'));
        NO NAME      
---------- ----------
        1 NAME1      
        2 NAME2      
    ...omitted
    29999 NAME29999
    30000 NAME30000

2. Char semantic comparison available 

<Example>
SQL> CREATE TABLE TEST (NO NUMBER, CHA CHAR(10));
SQL> INSERT INTO TEST 
     SELECT LEVEL, 'CHA'||LEVEL FROM DUAL CONNECT BY LEVEL < 1000001;
SQL> COMMIT;
SQL> SELECT * FROM TEST WHERE CHA IN ('CHA1','CHA2',
     ...omitted
     'CHA29999','CHA30000');
        NO CHA
---------- ----------
        1 CHA1
        2 CHA2
   ...omitted
   29999 CHA29999
   30000 CHA30000
SQL> SELECT * FROM TEST WHERE (NO,CHA) IN ((1,'CHA1'),(2,'CHA2'),...omitted
     (29999,'CHA29999'),(30000,'CHA30000'));
        NO NAME
---------- --------------------
        1 CHA1
        2 CHA2
    ...omitted
    29999 CHA29999
    30000 CHA30000

3. When the DATA TYPE of the column in the IN condition does not change 

<Example>
SQL> CREATE TABLE TEST (NO NUMBER, NAME VARCHAR(10));
SQL> INSERT INTO TEST VALUES (1,1);
SQL> INSERT INTO TEST VALUES (2,2);
SQL> INSERT INTO TEST VALUES (3,3);
SQL> SELECT * FROM TEST WHERE NAME IN (1,2,3);
        NO NAME
---------- ----------
        1 1

        3 3

SQL> INSERT INTO TEST VALUES (4,'A');
SQL> SELECT * FROM TEST WHERE NAME IN (1,2,3);
TBR-5074: Given string does not represent a number in proper format.
-> When only numbers are in the IN list, automatic type conversion is performed and output is shown,
   but if there is a value that cannot be converted to a number, execution is not possible.

 

Caution

If the above conditions are not met, the general expression restrictions apply.

(There is a limit of 65535 temporary values required when evaluating a single expression.)

In this case, the number of limits will vary depending on the use of expressions.

 

 

 

Related to