# ESQL to compare data in two indexes based on a unique key

**URL:** <https://discuss.elastic.co/t/esql-to-compare-data-in-two-indexes-based-on-a-unique-key/369211>\
**Category:** Kibana\
**Tags:** esql\
**Created:** [October 22, 2024, 11:20am UTC](https://discuss.elastic.co/t/esql-to-compare-data-in-two-indexes-based-on-a-unique-key/369211 "2024-10-22T11:20:38Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![venkatkumar229](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/venkatkumar229/32/104663_2.png) [@venkatkumar229](https://discuss.elastic.co/u/venkatkumar229)\
**Post date:** [October 22, 2024, 11:20am UTC](https://discuss.elastic.co/t/esql-to-compare-data-in-two-indexes-based-on-a-unique-key/369211/1 "2024-10-22T11:20:38Z")

</div>

Hi Team,

I am having two indexes in elasticsearch/kibana and wanted to write a ESQL query which will fetch the documents based on a unique field where the documents are there in index1 but not in index2. Could you please let me know if we can achieve this using ESQL.

For easy understanding SQL format:

```auto
SELECT unique_key
FROM "index1"
WHERE unique_key NOT IN (
    SELECT unique_key 
    FROM "unique_key"
)

```

---

<div class="post-metadata">

**Author:** ![costin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/costin/32/44950_2.png) [@costin](https://discuss.elastic.co/u/costin)\
**Post date:** [February 11, 2025, 2:38pm UTC](https://discuss.elastic.co/t/esql-to-compare-data-in-two-indexes-based-on-a-unique-key/369211/2 "2025-02-11T14:38:30Z")

</div>

Late reply, hopefully it's still useful  
you can filter indices based on their name:

```auto
FROM index* METADATA _index
| WHERE _index == "index1"
| KEEP unique_key

```