notIn/NOTIN
The NOTIN is the inverse operation of IN, used to determine whether a specified value does not match any value in a subquery or list. It is equivalent to the function notIn and the predicate NOT IN. NOTIN supports both in-memory tables and DFS tables.
Syntax
select col(s)
from table
where col notIn (value1, value2, ...)or
select col(s)
from table
where col notIn (subquery)Arguments
col(s) is column name(s) to select. It can be one or more column names or
* (indicating all columns).
table is the table name to query.
col is the column to be filtered.
value1, value2, .../subquery is the value(s) to be filtered.
Examples
Query records where the sym column values are not equal to APPL and AMZN:
t = table(`APPL`AMZN`IBM`IBM`APPL`AMZN as sym, 1.8 2.3 3.7 3.1 4.2 2.8 as price);
select * from t where sym notIn (`APPL, `AMZN)
// equivalent to select * from t where sym not in (`APPL, `AMZN)| sym | price |
|---|---|
| IBM | 3.7 |
| IBM | 3.1 |
t1=table(`APPL`AMZN`IBM`IBM`APPL`AMZN as sym, 200 500 300 350 240 580 as vol);
select * from t where sym notIn (select sym from t1 where sym=`IBM)| sym | price |
|---|---|
| APPL | 1.8 |
| AMZN | 2.3 |
| APPL | 4.2 |
| AMZN | 2.8 |