How to do a specific type of join for two tables in SQL Server?

All we need is an easy explanation of the problem, so here it is.


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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.!18/76ced/32

select coalesce(r.week, w.week) week, 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  = 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 = 'Monday' then 1 when = '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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply