# Can we use SQL Join in elastic query? **URL:** \ **Category:** Elasticsearch\ **Created:** [August 22, 2019, 5:58pm UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378 "2019-08-22T17:58:52Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Sajal](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@Sajal](https://discuss.elastic.co/u/Sajal)\ **Post date:** [August 22, 2019, 5:58pm UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/1 "2019-08-22T17:58:52Z")
Hi Can we use join function in elastic query ? I want to join two indexes with one common field . How to do that ? BR// Sajal ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 22, 2019, 7:32pm UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/2 "2019-08-22T19:32:11Z")
Elasticsearch does not support joins at all. ---
**Author:** ![Sajal](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@Sajal](https://discuss.elastic.co/u/Sajal)\ **Post date:** [August 23, 2019, 2:41am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/3 "2019-08-23T02:41:05Z")
Hi Christian , What is workaround for JOIN in ES ? Is it possible in some other way ? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 23, 2019, 4:09am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/4 "2019-08-23T04:09:36Z")
Most common workaround is to denormalize the data into a form that matches what you want returned when you search. ---
**Author:** ![Sajal](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@Sajal](https://discuss.elastic.co/u/Sajal)\ **Post date:** [August 23, 2019, 4:15am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/5 "2019-08-23T04:15:32Z")
Hi Christian , how to do denormalisation ? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 23, 2019, 4:18am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/6 "2019-08-23T04:18:46Z")
That depends on the data so it would help to know a bit about it. [This article](https://en.wikipedia.org/wiki/Denormalization) provides some background. ---
**Author:** ![Sajal](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@Sajal](https://discuss.elastic.co/u/Sajal)\ **Post date:** [August 23, 2019, 4:25am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/7 "2019-08-23T04:25:27Z")
Hi Christian , Take a example : If username in both index is same then add both indexes and create new one t \_index windows-6.5.4-2019.08 t windows.event\_data.TargetLogonId 0xhhg9a t windows.event\_data.TargetUserName sajal t windows.event\_data.TargetUserSid 1-2-3-4 t windows.event\_data.TokenElevationType %%8765 * * * t \_index mac-ls-2019.08.\* t s-action Pipe t username sajal t x-http-connect-host [us.com](http://us.com) t x-http-connect-port 443 t x-virus-details - * * * ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 23, 2019, 4:27am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/8 "2019-08-23T04:27:58Z")
How would you join that given that you probably could have lots of entries in each index for a single user name? Just on user name? Would the timestamp be relevant given that you have 2 time based indices? ---
**Author:** ![Sajal](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@Sajal](https://discuss.elastic.co/u/Sajal)\ **Post date:** [August 23, 2019, 4:34am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/9 "2019-08-23T04:34:36Z")
yes i have two different indexes with timestamp . You can take username as it won't repeat. Just add both indexes and create new one ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 23, 2019, 4:39am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/10 "2019-08-23T04:39:04Z")
Then merge the documents into a single one at index time. ---
**Author:** ![Sajal](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@Sajal](https://discuss.elastic.co/u/Sajal)\ **Post date:** [August 23, 2019, 4:40am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/11 "2019-08-23T04:40:17Z")
i am new to this . can you please help me how to do this ? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 23, 2019, 4:42am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/12 "2019-08-23T04:42:42Z")
How are you indexing your data into Elasticsearch? What is the source of these different documents? Are they generated close in time? Is it always a 1-to-1 mapping? ---
**Author:** ![Sajal](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@Sajal](https://discuss.elastic.co/u/Sajal)\ **Post date:** [August 23, 2019, 4:44am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/13 "2019-08-23T04:44:01Z")
logs are coming from filebeat. They are generated close in time and yes always 1 to1 mapping ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 23, 2019, 5:00am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/14 "2019-08-23T05:00:28Z")
To do this at index time you probably need to send the data through Logstash as that offers greater flexibility. If it is truly a 1:1 mapping and the user name does not appear in multiple documents, you could set the document ID to the user name and perform an upsert (think Logstash supports this but have not tried) for both documents. You should then end up with a single document containing all the fields. Another option might be to use the aggregate filter, similar to what is described in [this thread](https://discuss.elastic.co/t/aggregation-filter-timeout-settings/196208/4). I would recommend opening a separate thread on this in the Logstash section. ---
**Author:** ![Sajal](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@Sajal](https://discuss.elastic.co/u/Sajal)\ **Post date:** [August 23, 2019, 5:10am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/15 "2019-08-23T05:10:33Z")
Just to update the correct requirement now . I have two indexes with particular date (Eg 23 August 2019) , when username in both index gets matched then get only last 10 logs only in new index ---
**Author:** ![ynuosoft](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ynuosoft/32/47305_2.png) [@ynuosoft](https://discuss.elastic.co/u/ynuosoft)\ **Post date:** [August 24, 2019, 2:30am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/16 "2019-08-24T02:30:32Z")
use Apache Hive Or Apache Spark ---
**Author:** ![Sajal](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@Sajal](https://discuss.elastic.co/u/Sajal)\ **Post date:** [August 24, 2019, 7:13am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/17 "2019-08-24T07:13:36Z")
Is this possible with nested query ? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 24, 2019, 7:32am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/18 "2019-08-24T07:32:45Z")
No, I don’t think so. ---
**Author:** ![Sajal](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@Sajal](https://discuss.elastic.co/u/Sajal)\ **Post date:** [August 24, 2019, 7:45am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/19 "2019-08-24T07:45:29Z")
can we apply nested query in two indexes ? If yes , what is the query syntax ? ![image](https://us1.discourse-cdn.com/elastic/original/3X/4/a/4a6b0002d60a0bcd5c0764c5bcdec171d5eb6a4a.png) ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 24, 2019, 8:32am UTC](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378/20 "2019-08-24T08:32:38Z")
No that is not possible. Nested queries requires all the parts to be in the same document. [Next page](https://discuss.elastic.co/t/can-we-use-sql-join-in-elastic-query/196378.md?page=2)