Join

###Join As you might now, elasticsearch does not supports join on elasticsearch-sql we implemented a limited support on join queries! We also implemented some hints to help with your join We supports both JOIN and LEFT_JOIN

To use Join you must specified JOIN or LEFT_JOIN on your query. (commas join not supported) The implementation is similar to Hash_join implementations on other data bases

####Limitations

1. Only 2 Tables(indices/types) Join support
2. On "ON" you can only use "AND" connections and EQUAL conditions.
3. You must use aliases for tables (acounts a )
4. On Where , don't combine decision trees combining both tables. For example This will work:
   WHERE (a.key1>3 OR a.key1<0) AND (b.key2 > 4 OR b.key2<-1)
   But we do not support this:
   WHERE (a.key1>3 OR b.key2<0) AND (a.key1 > 4 OR b.key2<-1)
5. No group by and order by support for result
6. Limit is allowed without offsets.

####Hints ######To use hints use them as comment

1. HASH_WITH_TERMS_FILTER

• use this Optimization if the 2nd table have lot of values and 1st table has low amount of unique values on join conditions
• example (will query elastic for dogs with filter age>1 and holdersName in (names from first fetch) )
  SELECT /*! HASH_WITH_TERMS_FILTER*/ a.firstname ,a.lastname , a.gender ,d.name FROM people a JOIN dog d on d.holdersName = a.firstname WHERE (a.age > 10 OR a.balance > 2000) AND d.age > 1

2. JOIN_TABLES_LIMIT(firstLimit,secondLimit)

• use this hint to limit results fetched from each table, use null for no limit.
  if you want to limit combined results , just use LIMIT on query.
• example (will only fetch 2 houses )
  select /*! JOIN_TABLES_LIMIT(null,2) */ c.name.firstname,c.parents.father , h.name,h.words from got/char c JOIN got/house h

####example from ui