LEFT JOIN not returning null when no row exists

Question

I'm working with 2 tables: 1 that deals with basic demographics for a group of people, and 1 that deals with activities that have been completed. What I need to do is pull all results (all people) from the demographics table, and then display activity info for anyone who has completed a certain type of activity within a given timeframe (it's also possible that a given person may have completed multiple activities in that timeframe). If a person has not completed any of the specified activities, I still want them to be visible in the list. I'd like the results to look like this:

PersonID  Name           DateOfBirth   ActivityDate   ActivityType
---------------------------------------------------------------------
1001      John Smith     01/01/1990    10/18/2022     Painting
1002      Jane Doe       12/31/1980    NULL           NULL
1003      Bob Brown      07/04/1995    10/17/2022     Reading
1003      Bob Brown      07/04/1995    09/09/2022     Painting
1004      Mike Jones     03/24/1984    NULL           NULL
1005      Ann Green      11/30/1988    08/29/2022     Writing
1006      Sally Black    05/15/1999    NULL           NULL

It seems like it should be really simple query with a LEFT JOIN between the two tables:

SELECT DISTINCT
    d.PersonID,
    d.Name
    d.DateOfBirth,
    a.ActivityDate
    a.ActivityType
FROM Demographics d
LEFT JOIN Activity a ON d.PersonID = a.PersonID
WHERE ActivityDate BETWEEN DATEADD(yy,-1,GETDATE()) AND GETDATE()
    AND ActivityType IN ('Painting','Reading','Writing')
ORDER BY d.PersonID, a.ActivityDate DESC

However, when I run this query, I'm only getting results for people who have actually completed activities (i.e. the people with NULL results in my example are missing).

PersonID  Name           DateOfBirth   ActivityDate   ActivityType
---------------------------------------------------------------------
1001      John Smith     01/01/1990    10/18/2022     Painting
1003      Bob Brown      07/04/1995    10/17/2022     Reading
1003      Bob Brown      07/04/1995    09/09/2022     Painting
1005      Ann Green      11/30/1988    08/29/2022     Writing

Again, I'd like to display all people from the demographics table, but then show the specified activities for those who have completed them.

Is something wrong with my join? Is the LEFT JOIN the correct way to go about this?

You are talking about the `ActivityDate` column or the `ActivityType` column? — Haim Abeles, Oct 18 '22 at 21:40
You where clause is based on your left joined (Activity) table, and you don't include null values in your where clause. So the join is working, but you are filtering any rows out without an activity. — Dale K, Oct 18 '22 at 21:40
Also to be clear need to show us sample data from your base tables, not just your desired results. — Dale K, Oct 18 '22 at 21:42
Does this answer your question? [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) — philipxy, Oct 19 '22 at 02:22
Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] — philipxy, Oct 19 '22 at 02:24
LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN a WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS This is a faq. But one must pin down via a [mre] & write many clear, concise & precise phrasings of one's question/problem/goal to search reasonably. PS [Re querying.](https://stackoverflow.com/a/33952141/3404097) — philipxy, Oct 19 '22 at 04:10
Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad definition/query/function with the obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & ideally a [mre]. But please ask about bad code 1st because misconceptions get in the way of your goal. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Jan 20 '23 at 22:09

score 5 · Answer 1 · answered Oct 18 '22 at 21:43

Your WHERE clause is based entirely on your left joined (Activity) table, and you don't allow NULL values anywhere in your WHERE clause. So the join is working, but you are filtering any rows out without an activity.

To correct this, change

WHERE ActivityDate BETWEEN DATEADD(yy,-1,GETDATE()) AND GETDATE()
    AND ActivityType IN ('Painting','Reading','Writing')

to

WHERE a.PersonId IS NULL 
OR (
    ActivityDate BETWEEN DATEADD(yy,-1,GETDATE()) AND GETDATE()
    AND ActivityType IN ('Painting','Reading','Writing')
)

score 0 · Answer 2 · answered Oct 18 '22 at 21:43

0

When you add the condition

ActivityType IN ('Painting','Reading','Writing'),

you actually forbid ActivityType of NULL which would be necessary to include people which have not finished any activity. Same for the condition on ActivityDate

Change your query as follows to also allow NULL values

SELECT DISTINCT
    d.PersonID,
    d.Name
    d.DateOfBirth,
    a.ActivityDate
    a.ActivityType

FROM Demographics d LEFT JOIN Activity a ON d.PersonID = a.PersonID
WHERE (ActivityDate IS NULL OR ActivityDate BETWEEN DATEADD(yy,-1,GETDATE()) AND GETDATE())
AND (ActivityType IS NULL OR ActivityType IN ('Painting','Reading','Writing'))
ORDER BY d.PersonID, a.ActivityDate DESC

answered Oct 18 '22 at 21:43

derpirscher

14,418
3
18
35

This makes sense. However, when I make these changes to my query, nothing changes with my results :( – EJF Oct 18 '22 at 21:50
1

That's quite hard to believe. Can you add some example data? – derpirscher Oct 18 '22 at 21:52
1

@EJF please create a DBFiddle to demonstrate this because based on what you have shown it doesn't appear possible, so there might be more to the problem than you have posted. – Dale K Oct 18 '22 at 21:55
1

Also the usage of `DISTINCT` may indicate some additional problems, because based on your current description, it shouldn't be necessary – derpirscher Oct 18 '22 at 21:58

score -2 · Answer 3 · edited Oct 19 '22 at 19:14

-2

Use "OR" for ActivityType column for "null" record in your existing query like below:

SELECT DISTINCT
    d.PersonID,
    d.Name,
    d.DateOfBirth,
    a.ActivityDate,
    a.ActivityType
FROM Demographics d
LEFT JOIN Activity a ON d.PersonID = a.PersonID
WHERE ActivityDate BETWEEN DATEADD(yy,-1,GETDATE()) AND GETDATE()
AND (ActivityType IN ('Painting','Reading','Writing') OR ActivityType  IS NULL)
ORDER BY d.PersonID, a.ActivityDate DESC

edited Oct 19 '22 at 19:14

Dale K

25,246
15
42
71

answered Oct 19 '22 at 07:09

Prabhakar Anand

7
2

How does this differ from existing answers? – Dale K Oct 19 '22 at 09:22
1

@DaleK It doesn't consider the `ActivityDate` could also be `NULL` ... – derpirscher Oct 19 '22 at 09:47

LEFT JOIN not returning null when no row exists

3 Answers3