SQL:比较缺少记录的两个表，然后比较日期字段

Question

我有如下两张表

工作任务

emp_id   | start_date  |   End Date
------------------------------------------
  1      | May-10-2017 | May-30-2017
  1      | Jun-05-2017 | null
  2      | May-08-2017 | null 

hourly_pay

emp_id   | start_date  |   End Date    |  Rate
-----------------------------------------------
  1      | May-20-2017 | Jun-30-2017   |  75
  1      | Jul-01-2017 | null          |  80

这两个表共享 emp_id(员工 ID)外键，加入这两个表我应该能够:

1. 查找 hourly_pay 表中缺失的员工记录。鉴于这里的数据，查询应该从 work_assignments 表中返回 emp_id 2
2. 查找 hourly_pay start_date 晚于工作分配 start_date 的记录。同样，鉴于这里的数据，查询应该返回 emp_id 1(因为 work_assignments.start_date 有 May-10-2017，而最早的 hourly_pay.start_date 是 May-20-2017)

我能够使用下面的连接查询获得结果的第一部分

select distinct emp_id from work_contracts
left join hourly_pay hr USING(emp_id)
where hr.emp_id is null 

我卡在了第二部分，我可能需要一个相关的子查询来告知在 work_assignments start_date 之前没有开始的时薪表记录？还是有其他办法？

Answer 1

在内部查询中进行日期比较，然后将其包装以将其过滤为满足延迟付款标准的日期。

select * from (
    select distinct c.emp_id, 
        case when c.start_date < hr.start_date then 1 else 0 end as latePay
    from work_contracts c
        left join hourly_pay hr USING(emp_id)
) result
where latePay = 1