title: How to join exclusively by date range in Hive SQL?
body: <p>I have two subqueries that i'd like to join only by the date range between open and closed date from the first table.</p>
  
  <p>First table example:</p>
  
  <pre><code>| id_original | open_datetime     | close_datetime    |
  |-------------|-------------------|-------------------|
  |      1      |2019-01-01 10:00:02|2019-01-02 11:00:21|
  |      2      |2019-01-01 10:05:52|2019-01-05 16:45:12|
  |      3      |2019-01-03 00:00:43|2019-01-03 23:12:44|
  </code></pre>
  
  <p>Second table example:</p>
  
  <pre><code>| category | all other columns...| open_date         |
  |----------|---------------------|-------------------|
  |    A     |        ...          |2019-01-01 11:00:00|
  |    B     |        ...          |2019-01-02 19:10:10|
  |    C     |        ...          |2019-01-03 08:23:45|
  |    D     |        ...          |2019-01-04 18:10:53|
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>| id_original | category | all other columns...| open_date         |
  |-------------|----------|---------------------|-------------------|
  |      1      |    A     |        ...          |2019-01-01 11:00:00|
  |      2      |    A     |        ...          |2019-01-01 11:00:00|
  |      2      |    B     |        ...          |2019-01-02 19:10:10|
  |      2      |    C     |        ...          |2019-01-03 08:23:45|
  |      2      |    D     |        ...          |2019-01-04 18:10:53|
  |      3      |    C     |        ...          |2019-01-03 08:23:45|
  </code></pre>
  
  <p>This is my code:</p>
  
  <pre><code>SELECT *
  FROM (
      SELECT id, open_datetime, close_datetime
      FROM table1
      WHERE id IN (list_of_ids)
  ) t1
  LEFT JOIN (
      SELECT *
      FROM table2
      WHERE other_conditions
  ) t2 ON t2.open_date &gt;= t1.open_datetime AND t2.open_date &lt;= t1.close_datetime
  </code></pre>
  
  <p>I know that Hive SQL doesn't support inequality as conditions for a <code>JOIN</code>. But how should I approach this matter?</p>
  
  <p>Note: The join I need is exclusively for dates, there is no equal key from t1 and t2 that I can use to join them.</p>
  
  <p>Thanks!</p>