JOIN tables on a date range, but show the missing dates?-CodePudding

I have two tables:

WEEK_DATE	DAY_NUMBER
8/17/2022	1
8/18/2022	2
8/19/2022	3
8/22/2022	4
8/23/2022	5
8/24/2022	6
8/25/2022	7
8/26/2022	8
8/29/2022	9

START_DATE	END_DATE	PERSON
8/17/2022	8/18/2022	Jack
8/23/2022	8/29/2022	Jack

I am trying to determine the week days that Jack missed, which would look like this:

PERSON	WEEK_DATE	DAY_NUMBER
Jack	8/19/2022	3
Jack	8/22/2022	4

Right now, this is my query and the results below:

SELECT
    b.START_DATE,
    b.END_DATE,
    b.PERSON,
    a.WEEK_DATE,
    a.DAY_NUMBER
FROM b
LEFT JOIN a
    ON a.WEEK_DATE BETWEEN b.START_DATE AND b.END_DATE

START_DATE	END_DATE	PERSON	WEEK_DATE	DAY_NUMBER
8/17/2022	8/18/2022	Jack	8/17/2022	1
8/17/2022	8/18/2022	Jack	8/18/2022	2
8/23/2022	8/29/2022	Jack	8/23/2022	5
8/23/2022	8/29/2022	Jack	8/24/2022	6
8/23/2022	8/29/2022	Jack	8/25/2022	7
8/23/2022	8/29/2022	Jack	8/26/2022	8
8/23/2022	8/29/2022	Jack	8/29/2022	9

How do I get the JOIN to include Jack's missed days as shown previously (days 3 and 4)? Even as nulls like this:

START_DATE	END_DATE	PERSON	WEEK_DATE	DAY_NUMBER
8/17/2022	8/18/2022	Jack	8/17/2022	1
8/17/2022	8/18/2022	Jack	8/18/2022	2
(null)	(null)	Jack	8/19/2022	(null)
(null)	(null)	Jack	8/22/2022	(null)
8/23/2022	8/29/2022	Jack	8/23/2022	5
8/23/2022	8/29/2022	Jack	8/24/2022	6
8/23/2022	8/29/2022	Jack	8/25/2022	7
8/23/2022	8/29/2022	Jack	8/26/2022	8
8/23/2022	8/29/2022	Jack	8/29/2022	9

CodePudding user response：

You have two options, either use a RIGHT JOIN:

SELECT
    b.START_DATE,
    b.END_DATE,
    b.PERSON,
    a.WEEK_DATE,
    a.DAY_NUMBER
FROM b
RIGHT JOIN a
    ON a.WEEK_DATE BETWEEN b.START_DATE AND b.END_DATE
--WHERE a.DAY_NUMBER IS NULL

Or, swap your table order:

SELECT
    b.START_DATE,
    b.END_DATE,
    b.PERSON,
    a.WEEK_DATE,
    a.DAY_NUMBER
FROM a
LEFT JOIN b
    ON a.WEEK_DATE BETWEEN b.START_DATE AND b.END_DATE
--WHERE a.DAY_NUMBER IS NULL

I would recommend the second option as pretty much everyone writes SQL to use LEFT JOINs, so they are the more easily understood option.

EDIT to add some further explanation:

The only time you'll ever want a LEFT JOIN or RIGHT JOIN are in cases like these where you need to conserve rows that would be filtered out by an INNER JOIN.

If you a look at a basic from/join:

FROM table a
JOIN table b

The top table a is the left table, the bottom table b is the right table. Same is generally true for later joins:

JOIN table a ...
JOIN table b on a.id = b.id

Table a is left, table b is right.

So if you want to conserve rows from table a, you would want a LEFT JOIN:

FROM table a
LEFT JOIN table b

If you wanted to conserve rows from table b, then we loop back to your original situation, and you could RIGHT JOIN:

FROM table a
RIGHT JOIN table b

But again, everyone always uses LEFT JOINs as the standard, so you'd be better off swapping the table order and using a left:

FROM table b
LEFT JOIN table a