Fiddle: http://sqlfiddle.com/#!18/76ced/2

I've got two tables - Roster and Sample. The Roster table contains information on what days an employee has worked across a period of two weeks. The Sample table contains a sample weekly roster.

I want to know how the days in Roster and Sample differ across each week of the Roster. Basically want something like the below table:

Week Roster Day Sample Day
1 NULL Monday
1 Tuesday Tuesday
1 Wednesday Wednesday
1 Thursday Thursday
2 Monday Monday
2 Tuesday Tuesday
2 Wednesday Wednesday
2 Thursday Thursday
2 Friday NULL

I've tried doing a simple outer join between the two tables but that did not give the desired result. It looks like I may need to do a cross join but I'm not sure how to incorporate that either. Any help would be appreciated.


1 Answer 1


You want to return rows from either side of the join when there is no matching row from the other row source, this requires a full outer join rather than just a standard left outer join.

Just doing the full join on it's own is not enough, as you still want to return a null for 'Monday' on week 1 even though there is a 'Monday' that would make the join. For this requirement you need to make sure both these columns are somehow involved in the full join. For this, you can cross join a distinct set of weeks and days to get a starting point.


select coalesce(r.week, w.week) week, r.day roster_day, d.sample_day
from (select distinct week from roster) w
cross join (select [Day] sample_day from sample) d
full join roster r
  on  r.week = w.week
 and  r.day  = d.sample_day
order by 1

Note that the ordering is going to be pseudorandom without you adding something (so that 'Monday' is considered less than 'Friday'). You could do this be ordering by a case when expression against the day columns (something like case when r.day = 'Monday' then 1 when r.day = 'Tuesday' then 2... etc). It is a happy coincidence (although not completely unexpected given the access paths available and small data sets) that the SQL Fiddle returns results in the order you might want.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.