SQL: Selecting between dates

Question

I have two tables that look like:

table A:
ID, target_date, target_ID

table B:
ID, target_ID, begin_date, end_date

Table B may have multiple records for the same target_ID but different date ranges. I am interested in a SQL query that is able to return target_dates that are not within the begin_date and end_date ranges for the given target_ID.

Answer 1

There is a trick to this. Look for the ones that match, using a left join, and then choose the ones that don't match:

select a.*
from tablea a left join
     tableb b
     on a.target_id = b.target_id and
        a.target_date between b.begin_date and b.end_date
where b.target_id is null;

You can express this in several different ways. For instance, not exists may also seem natural:

select a.*
from tablea a
where not exists (select 1
                  from tableb b
                  where a.target_id = b.target_id and
                        a.target_date between b.begin_date and b.end_date
                 );

Note: I am using between for these comparisons as a convenient shorthand (to match the language you use in the question). Often with dates, explicit use of <, <=, >, or >= is preferred.

Answer 2

SELECT A.target_date 
FROM A LEFT OUTER JOIN B 
     ON (A.target_ID=B.target_ID 
     AND A.target_date>=B.begin_date 
     AND A.target_date<=B.end_date) 
WHERE B.begin_date IS NULL

Answer 3

Maybe:

SELECT target_date FROM A 
   INNER JOIN B 
   ON A.target_ID = B.target_ID
WHERE target_date NOT BETWEEN begin_date AND end_date