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

I have two tables:

a

b

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

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

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

>Solution :

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