title: Filtering a large table by date
body: <p>I have a table, VISIT_INFO, with these columns:</p>
  
  <pre><code>pers_key - unique identifyer for each person
  pers_name - name of person
  visit_date - date at which they visited a business
  </code></pre>
  
  <p>And another table, VALID_DATES, with these columns:</p>
  
  <pre><code>condition - string
  start_date - date
  end_date - date 
  </code></pre>
  
  <p>I currently have the following query:</p>
  
  <pre><code>select pers_key, pers_name from VISIT_INFO a
  CROSS JOIN
  (select start_date, end_date from VALID_DATES where condition = 'condition1') b
  WHERE (a.visit_date &gt;= b.start_date and a.visit_date &lt;= b.end_date)
  GROUP BY a.pers_key
  </code></pre>
  
  <p>So 'condition1' has a specific start_date and end_date. I need to filter VISIT_INFO for visits that are between the two dates. I'm wondering if there is a more efficient way to do this. From my current understanding, it currently has to go through the entire table (millions of rows) and add start_date and end_date to each row. Then does it have to go through each row again and test against the WHERE condition? </p>
  
  <p>I ask this because when I remove the cross join and hardcode the start_date and end_date for condition1, it takes substantially less time. I'm trying to avoid hardcoding in the dates because it will lead to serious tedium down the road. </p>
  
  <p>So to reiterate, is there a better way to filter VISIT_INFO by specific dates in VALID_DATES?   </p>
  
  <p>Edit: I just realized I left out a pretty huge piece of information, being that this is all in HIVE. So EXISTS and joins on (a between b and c) are out of the question.</p>