SQL: Comparing two tables for missing records and then on the date fields

Question

I have two tables as below

work_assignments

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

These 2 tables share the emp_id (employee id) foreign key and joining these two I should be able to:

1. find employee records missing in the hourly_pay table. Given the data here, the query should return emp_id 2 from work_assignments table
2. find the records where the hourly_pay start_date that are later than the work assignments start_date. Again, given the data here, the query should return emp_id 1 (because work_assignments.start_date has May-10-2017, while the earliest hourly_pay.start_date is on May-20-2017)

I am able to achieve the first part of result using the join query below

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

I am stuck on the second part where probably I need a correlated subquery to tell the hourly pay table records that did not start before the work_assignments start_date? or is there any other way?

Answer 1

Do the date comparison in an inner query then wrap it to filter it to the ones that satisfy the late pay criteria.

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