SQL combine two tables with date interval

Question

I have 2 tables with start and end dates. I have to join then in a particular way:

Table A:

ID	Start Date	End Date
1	01/01/2024	10/01/2024
1	10/01/2024	15/01/2024
1	15/01/2024	20/01/2024
2	01/01/2024	10/01/2024
2	10/01/2024	20/01/2024

Table B:

ID	Start Date	End Date
1	10/01/2024	11/01/2024
1	12/01/2024	18/01/2024
2	10/01/2024	10/01/2024

Output:

ID	Start Date (A)	End Date (A)	Start Date (B)	End Date (B)
1	01/01/2024	10/01/2024	NULL	NULL
1	10/01/2024	15/01/2024	10/01/2024	11/01/2024
1	10/01/2024	15/01/2024	12/01/2024	18/01/2024
1	15/01/2024	20/01/2024	NULL	NULL
2	10/01/2024	20/01/2024	10/01/2024	10/01/2024

Can you find the correct SQL query with WHERE condition to obtain the desired output? Without using RANK or ROW_NUMBER.

Answer 1

Here's a solution using LEFT JOIN.

If there is no matching row in TableB, we still include the row from TableA with NULL values for StartDate_B and EndDate_B.

  SELECT 
    A.ID,
    A.StartDate AS StartDate_A,
    A.EndDate AS EndDate_A,
    B.StartDate AS StartDate_B,
    B.EndDate AS EndDate_B
FROM 
    TableA A
LEFT JOIN 
    TableB B ON A.ID = B.ID
            AND (
                (B.StartDate >= A.StartDate AND B.StartDate < A.EndDate) OR
                (B.EndDate > A.StartDate AND B.EndDate <= A.EndDate) OR
                (B.StartDate <= A.StartDate AND B.EndDate >= A.EndDate)
            )
WHERE
    B.ID IS NOT NULL
    OR (B.ID IS NULL AND A.ID IS NOT NULL);

Demo: https://dbfiddle.uk/_YYKgFCo