How to correctly join two tables that each have Start Date and Stop Date columns?

Question

This should be easy, maybe even a duplicate, but neither my brain nor my search engine is being very helpful today.

My problem is as below:

Given two tables that both have:

1. A start date
2. A stop date
3. A property of an employee. Let's say Desk Number in table A and Team in table B
4. The employee's unique ID number.

join together the two tables in some honest way such that you only need one start and stop date per row in the resulting table.

Example input and desired output: Table A:

Start Date	Stop Date	ID	Desk Number
01/20	05/20	0100	55
03/20	06/20	0100	56
02/22	04/22	0200	91

Table B (notice the overlap in employee 0100's dates):

Start Date	Stop Date	ID	Team Number
01/20	04/20	0100	2
02/20	06/20	0100	3
02/22	04/22	0200	8

Example output:

Start Date	Stop Date	ID	Desk Number	Team Number
01/20	04/20	0100	55	2
02/20	05/20	0100	55	3
02/20	06/20	0100	56	3
02/22	04/22	0200	91	8

I can handle manipulating the resulting dates once the tables are joined correctly, but the join in of itself is causing me issues. I just can't figure out how to make sure that some dates that fall outside of the range don't slip in. My currently solution for the join, which I'm unsure of, was to just join on

[Table A].[Start Date] <= [Table B].[Stop Date]
[Table B].[Start Date] <= [Table A].[Stop Date]

and then take the max/min date as appropriate (don't worry about that part, I only care about the join), but I very much doubt that the solution could really be that simple.

Answer 1

Assuming I'm not getting something wrong here, the desired output should be

Start Date	Stop Date	ID	Desk Number	Team Number
01/20	04/20	0100	55	2
02/20	05/20	0100	55	3
03/20	04/20	0100	56	2
03/20	06/20	0100	56	3
02/22	04/22	0200	91	8

Your intuition as to the join is indeed correct, this produces the right result:

SELECT CASE
         WHEN teams.start_date > desks.start_date
         THEN teams.start_date
         ELSE desks.start_date
       END start_date
     , CASE
         WHEN teams.stop_date < desks.stop_date
         THEN teams.stop_date
         ELSE desks.stop_date
       END stop_date
     , desks.id
     , desks.desk_number
     , teams.team_number
  FROM desks
  JOIN teams
    ON desks.start_date < teams.stop_date
   AND teams.start_date < desks.stop_date
   AND teams.id = desks.id

Working demo on dbfiddle