# notIn {#notin}

**Parent topic:**[Functions](../../Functions/category.md)

## Syntax {#syntax}

`notIn(X, Y)`

## Arguments {#arguments}

**X** is a scalar, vector, tuple, matrix, array vector, dictionary, in-memory table with one column, keyed table, or indexed table.

**Y** is a scalar, vector, dictionary, in-memory table with one column, keyed table, or indexed table.

## Details {#details}

-   If *Y* is a scalar:
    -   If *Y* is of temporal types, check if each element in *X* is not in*Y*;
    -   If *Y* is a scalar of other data types, check if *X* and *Y* are not equal.
    -   If *Y* is a null value, return true.
-   If *Y* is a vector, check if each element of *X* is notin *Y*.
-   If *Y* is a dictionary, check if each element of *X* is not a key in the dictionary *Y*.
-   If *Y* is an in-memory table with one column, check if each element of *X* is not a value in the column of *Y*. Note the column cannot be array vector.
-   If *Y* is a keyed table or an indexed table, check if each element of *X* is not a key of *Y*. The number of elements in *X* must equal the number of key columns of *Y*.

## Examples {#examples}

``` {#codeblock_zq1_tms_c2c}
x=18:21:35+0..2
y=18:21:35
notIn(x,y)
// output: [false,true,true]

notIn(3 3 5 2, 2 3);
// output: [false,false,true,false]

x=dict(INT,DOUBLE);
x[1, 2, 3]=[4.5, 6.6, 3.2];
x;
/* output
3->3.2
1->4.5
2->6.6
*/

notIn(1..6, x);
// output: [false,false,false,true,true,true]

t = table(1 3 5 7 9 as id)
2 3 notIn t
// output: [true,false]

kt = keyedTable(`name`id,1000:0,`name`id`age`department,[STRING,INT,INT,STRING])
insert into kt values(`Tom`Sam`Cindy`Emma`Nick, 1 2 3 4 5, 30 35 32 25 30, `IT`Finance`HR`HR`IT)
notIn((`Tom`Cindy, 1 3), kt);
// output: [false,false]

t1 = indexedTable(`sym`side, 10000:0, `sym`side`price`qty, [SYMBOL,CHAR,DOUBLE,INT])
insert into t1 values(`IBM`MSFT`GOOG, ['B','S','B'], 10.01 10.02 10.03, 10 10 20)
notIn((`IBM`MSFT, ['S','S']), t1);
// output: [true,false]
```

When *X* is a floating-point number and *Y* is an integer, *X* will be converted to the data type of *Y*.

``` {#codeblock_apv_tb1_d2c}
notIn(10, NULL)
// output: true

notIn('a', 97)
// output: false

notIn(1, 1.1 1.2 1.3)
// output: true

notIn(float(1.1 2.2 3.3 4.4 5.5 6.6 7.7 8.8), 1..9)
// output: [false,false,false,false,false,false,false,false]
```

`in` can be used SQL SELECT for range filtering.

``` {#codeblock_mfk_5b1_d2c}
select * from kt where name notIn [`Tom, `Cindy];
```

|name|id|age|department|
|----|---|---|----------|
|Sam|2|35|Finance|
|Emma|4|25|HR|
|Nick|5|30|IT|

`notIn` can also be applied to queries on DFS tables:

``` {#codeblock_ofp_vb1_d2c}
login(`admin,`123456)
dbName="dfs://database1"
if(existsDatabase(dbName)){
	dropDatabase(dbName)
}
db=database(dbName,VALUE,2019.01.01..2019.01.03)
n=100
datetime=take(2019.01.01 +0..100,n)
sym = take(`C`MS`MS`MS`IBM`IBM`IBM`C`C$SYMBOL,n)
price= take(49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29,n)
qty = take(2200 1900 2100 3200 6800 5400 1300 2500 8800,n)
t=table(datetime, sym, price, qty)
trades=db.createPartitionedTable(t,`trades,`datetime).append!(t)

select * from trades where sym notIn `IBM`C
```

Related function: [in](../i/in.md)

