Joins

Open Distro for Elasticsearch SQL supports inner joins, left outer joins, and cross joins. Joins have a number of constraints:

  1. You can only join two indices.
  2. You must use aliases for indices (e.g. people p).
  3. Within an ON clause, you can only use AND conditions.
  4. In a WHERE statement, don’t combine trees that contain multiple indices. For example, the following statement works:

    WHERE (a.type1 > 3 OR a.type1 < 0) AND (b.type2 > 4 OR b.type2 < -1)
    

    The following statement does not:

    WHERE (a.type1 > 3 OR b.type2 < 0) AND (a.type1 > 4 OR b.type2 < -1)
    
  5. You can’t use GROUP BY or ORDER BY for results.
  6. LIMIT with OFFSET (e.g. LIMIT 25 OFFSET 25) is not supported.