# NOT IN replacement in elasticsearch sql query
**URL:** https://discuss.elastic.co/t/not-in-replacement-in-elasticsearch-sql-query/364463
**Category:** Elasticsearch
**Created:** [August 6, 2024, 10:10am UTC](https://discuss.elastic.co/t/not-in-replacement-in-elasticsearch-sql-query/364463 "2024-08-06T10:10:48Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@HelloWorld3](https://discuss.elastic.co/u/HelloWorld3)
#### Post date: [August 6, 2024, 10:10am UTC](https://discuss.elastic.co/t/not-in-replacement-in-elasticsearch-sql-query/364463/1 "2024-08-06T10:10:48Z")
I have an sql query which filters tables with conditions like not in, i want the same query for elasticsearch sql access.
SELECT TABLE\_SCHEMA, TABLE\_NAME FROM INFORMATION\_SCHEMA.TABLES WHERE TABLE\_TYPE = 'BASE TABLE'AND TABLE\_SCHEMA NOT IN(" + schema + ") AND TABLE\_NAME LIKE '%" + text + "%' ORDER BY TABLE\_NAME ASC".
Can anyone please provide any suitable solution. I learnt that in , not in or subqueries of advanced filtering are also not supported.
---
### Author:  [@Wolfram\_Haussig](https://discuss.elastic.co/u/Wolfram_Haussig)
#### Post date: [August 6, 2024, 10:38am UTC](https://discuss.elastic.co/t/not-in-replacement-in-elasticsearch-sql-query/364463/2 "2024-08-06T10:38:15Z")
Hi,
You could try if `rlike` (see [docs](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-like-rlike-operators.html#sql-rlike-operator)) is fast enough for your requirements:
```sql
SELECT
TABLE_SCHEMA, TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
TABLE_SCHEMA NOT RLIKE " + schema + " AND
TABLE_NAME LIKE '%" + text + "%'
ORDER BY
TABLE_NAME ASC
```
where `schema` is a string similar to: `SYS|SCHEMA1|SCHEMA2`
Best regards
Wolfram
---
### Author:  [@HelloWorld3](https://discuss.elastic.co/u/HelloWorld3)
#### Post date: [August 6, 2024, 10:47am UTC](https://discuss.elastic.co/t/not-in-replacement-in-elasticsearch-sql-query/364463/3 "2024-08-06T10:47:59Z")
No, I don't think rlike will suit my requirement, I want the extracted table to me not present in the specific schema..rlike is something like regex filtering for like..I don't want that
My current basic sql query is -- SHOW TABLES LIKE '%{table\_name}%'
---
### Author:  [@Wolfram\_Haussig](https://discuss.elastic.co/u/Wolfram_Haussig)
#### Post date: [August 6, 2024, 11:06am UTC](https://discuss.elastic.co/t/not-in-replacement-in-elasticsearch-sql-query/364463/4 "2024-08-06T11:06:25Z")
Then I am either missing something or your example is wrong - my code does exactly the same as yours:
> TABLE\_SCHEMA NOT IN(" + schema + ")
> TABLE\_SCHEMA NOT RLIKE " + schema + "
---
### Author:  [@HelloWorld3](https://discuss.elastic.co/u/HelloWorld3)
#### Post date: [August 6, 2024, 11:26am UTC](https://discuss.elastic.co/t/not-in-replacement-in-elasticsearch-sql-query/364463/5 "2024-08-06T11:26:14Z")
Hi,
The thing is the query I have given initially doesn't work in my elasticsearch sql access engine at all, see I am currently only able to use query like SHOW TABLES or SHOW CATALOG, and when I give the rlike query in support with SHOW TABLES as SHOW TABLES NOT RLIKE {1}, or SELECT TABLES NOT RLIKE {1}, And i also need to combine this with a like %sample\_text% ,.. it provides an exception like this---
"type": "parsing\_exception",
"reason": "line 1:13: mismatched input 'NOT' expecting {, 'ANALYZE', 'ANALYZED', 'CATALOG', 'CATALOGS', 'COLUMNS', 'CURRENT\_DATE', 'CURRENT\_TIME', 'CURRENT\_TIMESTAMP', 'DAY', 'DEBUG', 'EXECUTABLE', 'EXPLAIN', 'FIRST', 'FORMAT', 'FULL', 'FUNCTIONS', 'GRAPHVIZ', 'HOUR', 'INCLUDE', 'INTERVAL', 'LAST', 'LIKE', 'LIMIT', 'MAPPED', 'MINUTE', 'MONTH', 'OPTIMIZED', 'PARSED', 'PHYSICAL', 'PIVOT', 'PLAN', 'RLIKE', 'QUERY', 'SCHEMAS', 'SECOND', 'SHOW', 'SYS', 'TABLES', 'TEXT', 'TOP', 'TYPE', 'TYPES', 'VERIFY', 'YEAR', IDENTIFIER, DIGIT\_IDENTIFIER, TABLE\_IDENTIFIER, QUOTED\_IDENTIFIER, BACKQUOTED\_IDENTIFIER}"
}. Can you tell me something that will suit this.. or am I wrong here somewhere