# When SQL JOIN command will be available in ElasticSearch? **URL:** \ **Category:** Elasticsearch\ **Created:** [August 2, 2018, 1:40pm UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772 "2018-08-02T13:40:57Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Md\_Ghulam\_khaja](https://avatars.discourse-cdn.com/v4/letter/m/278dde/32.png) [@Md\_Ghulam\_khaja](https://discuss.elastic.co/u/Md_Ghulam_khaja)\ **Post date:** [August 2, 2018, 1:40pm UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/1 "2018-08-02T13:40:57Z")
Any guesses? ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [August 2, 2018, 10:23pm UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/2 "2018-08-02T22:23:31Z")
SQL is not all about joins 🙂 I don't believe this is on our roadmap at all, because it's not as simple as just adding the command to the SQL interface. ---
**Author:** ![Md\_Ghulam\_khaja](https://avatars.discourse-cdn.com/v4/letter/m/278dde/32.png) [@Md\_Ghulam\_khaja](https://discuss.elastic.co/u/Md_Ghulam_khaja)\ **Post date:** [August 3, 2018, 7:28am UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/3 "2018-08-03T07:28:15Z")
You mean to say in future releases SQL JOIN support is not on the queue, rest other SQL commands will be available. Why Elastic can not give support of JOIN's? ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [August 3, 2018, 7:30am UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/4 "2018-08-03T07:30:18Z")
> [@Md\_Ghulam\_khaja](#): > > Why Elastic can not give support of JOIN's? As I said, it's not just as simple as providing it as a SQL command. Elasticsearch is a distributed system, and to do a join is Not A Simple Thing. ---
**Author:** ![Md\_Ghulam\_khaja](https://avatars.discourse-cdn.com/v4/letter/m/278dde/32.png) [@Md\_Ghulam\_khaja](https://discuss.elastic.co/u/Md_Ghulam_khaja)\ **Post date:** [August 3, 2018, 7:36am UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/5 "2018-08-03T07:36:57Z")
Ok, I was waiting for ElasticSearch to give support of SQL joins which would make life easier. However, Can we do task close to SQL joins in ElasticSearch with query DSL? ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [August 3, 2018, 10:58am UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/6 "2018-08-03T10:58:56Z")
> [@Md\_Ghulam\_khaja](#): > > Can we do task close to SQL joins in Elasticsearch with query DSL? The question is "Why do you think you need Joins to solve your use case?". Could explain what your use case is? May be with a simple example? ---
**Author:** ![Md\_Ghulam\_khaja](https://avatars.discourse-cdn.com/v4/letter/m/278dde/32.png) [@Md\_Ghulam\_khaja](https://discuss.elastic.co/u/Md_Ghulam_khaja)\ **Post date:** [August 3, 2018, 11:45am UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/7 "2018-08-03T11:45:16Z")
I will try to be as simple as possible. I have two tables, say table1 and table2. These tables are joined using One to One relationship. I have indexed these two tables data in elasticsearch as doc1 and doc2 using logstash jdbc input. Without join how I will find common data in doc1 and doc2? ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [August 3, 2018, 12:17pm UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/8 "2018-08-03T12:17:23Z")
I know what a relational database is. I did most of my experience on that. Having tables is not a use case. That's an implementation detail. What is the use case? What is the content of table 1 and table 2? What kind of data are your users searching for? What are the properties they need to search with? ---
**Author:** ![Md\_Ghulam\_khaja](https://avatars.discourse-cdn.com/v4/letter/m/278dde/32.png) [@Md\_Ghulam\_khaja](https://discuss.elastic.co/u/Md_Ghulam_khaja)\ **Post date:** [August 3, 2018, 12:41pm UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/9 "2018-08-03T12:41:13Z")
Here is the use case. We have two tables port(1 million records) and physicalport(3 million records). port table contains column hortname and physicalport table contains alias. In column hostname data is same as in column alias. Ex hotname: CHHBHARTH02 in some row number alias : CHHBHARTH02 in some row number We have to find common data in these two tables. As in above example one data is common. If we do this in MySQL it takes lots of time to give result, So we want to experiment with Elasticsearch to get the result faster. ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [August 3, 2018, 1:11pm UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/10 "2018-08-03T13:11:28Z")
> We have to find common data in these two tables. If I fully understand the use case, there is no easy way to do that in elasticsearch IMO. Unless someone else has an idea. ---
**Author:** ![Md\_Ghulam\_khaja](https://avatars.discourse-cdn.com/v4/letter/m/278dde/32.png) [@Md\_Ghulam\_khaja](https://discuss.elastic.co/u/Md_Ghulam_khaja)\ **Post date:** [August 3, 2018, 1:25pm UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/11 "2018-08-03T13:25:05Z")
Ok. Let's wait till someone see this and reply with a solution. BTW Thanks David. ---
**Author:** ![varunnatraaj](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/varunnatraaj/32/23113_2.png) [@varunnatraaj](https://discuss.elastic.co/u/varunnatraaj)\ **Post date:** [August 7, 2018, 12:59pm UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/12 "2018-08-07T12:59:26Z")
If I understand this correctly, you could easily solve this by normalising the data. A hostname table containing the hostname with an auto-generated ID first. Then the port and physical port tables use the ID in place of the actual string (ID of CHHBHARTH02 in hostname/alias column instead of that word). With proper indexes in places, joins should be much faster as well as the queries - normal and analytics type. This is a typical RDBMS use case and 1-3 million data is not huge at all, so queries and joins should be fast. I'm not sure why you're trying to do this on ES. ---
**Author:** ![Md\_Ghulam\_khaja](https://avatars.discourse-cdn.com/v4/letter/m/278dde/32.png) [@Md\_Ghulam\_khaja](https://discuss.elastic.co/u/Md_Ghulam_khaja)\ **Post date:** [August 8, 2018, 9:31am UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/13 "2018-08-08T09:31:45Z")
We want to experiment with ES to do similar kind of comparison which we do on relational tables. Can somebody tell if It is right way to send bulk compare request using **client.msearch** API with term query? Here is What I am doing. 1. I am reading first doc 1 lac records from doc1 using ES javascript APIs. 2. Instead of sending one by one compare request on ES I am doing bulk compare using msearch. 3. I have divided 1 lac compare requests into 5 msearch API calls and calculating time of each response. For 1 lac compare its taking 12 seconds but on MySQL its taking 4 seconds. Is ES is for such kind of comparisons? ---
**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 8, 2018, 9:39am UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/14 "2018-08-08T09:39:00Z")
> [@Md\_Ghulam\_khaja](#): > > Is ES is for such kind of comparisons? Not really I would say. Comparing large sets of data to me this sounds like a type of analysis that might be better performed in a relational database with proper indexing. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 5, 2018, 9:39am UTC](https://discuss.elastic.co/t/when-sql-join-command-will-be-available-in-elasticsearch/142772/15 "2018-09-05T09:39:01Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.