SQL JOIN not showing NULL results when WHERE clause used

Question

Server: SQL Server 2008 R2
SQL Fiddle: http://sqlfiddle.com/#!6/112a6/4/0

I apologize for the poor subject/title, but I'm having a hard time verbalizing how to state my problem, so I'll draw it out with a (probably) lengthy example:

Below is a rather simple query that gets the number of sessions for a PC for a certain date range. Note at this point that the WHERE clause is commented out. What I'm hoping to receive is the full list of PCs, even those that have ZERO/NULL sessions during that range. However, when I add that WHERE clause I ONLY get the PCs that had sessions during that range. This is problematic because I can't see the PCs that were unused during that period.

SELECT 
    f_assetnetbiosname, 
    COUNT(f_sessiondate) as 'Sessions'
FROM 
    tb_assets ass
LEFT JOIN 
    tb_sessions ses
  ON 
    ses.f_sessionnetbiosname = ass.f_assetnetbiosname  
--WHERE (f_sessiondate BETWEEN '04/01/2015' AND '04/30/2015')
GROUP BY f_assetnetbiosname

========================   =======================================|
| tb_assets            |   | tb_sessions                          |
========================   =======================================|
|   f_assetnetbiosname |   |  f_sessionnetbiosname  f_sessiondate |
------------------------   ---------------------------------------|
|     COMP_001         |   |    COMP_002            03/29/2015    |
|     COMP_002         |   |    COMP_002            03/30/2015    |
|     COMP_003         |   |    COMP_001            03/30/2015    |
------------------------   |    COMP_001            04/02/2015    |
                           |    COMP_001            04/03/2015    |
                           |    COMP_001            04/04/2015    |
                           |    COMP_001            04/05/2015    |
                           -----------------------------------

The query based on these results returns the following result set:

|=================================|
| f_assetnetbiosname  | Sessions  |
|=================================|
| COMP_001            | 5         |
| COMP_002            | 2         |
| COMP_003            | 0         |
|=================================|

The problem is that I need to actually search only for a date range. So if I uncomment out the "WHERE" clause in the query above, the result set looks only like the following:

|=================================|
| f_assetnetbiosname  | Sessions  |
|=================================|
| COMP_001            | 4         |
|=================================|

But I need it to look like the following so that I can see the PCs that had zero sessions:

|=================================|
| f_assetnetbiosname  | Sessions  |
|=================================|
| COMP_001            | 4         |
| COMP_002            | 0         |
| COMP_003            | 0         |
|=================================|

Does anybody have a fix for this? Note that the example above is just that - an example. The real query is a multi-CTE bearcat but I figured it better to whittle this down to the basics so that I don't cloud the conversation by showing off my bad code.

SQL Fiddle: http://sqlfiddle.com/#!6/112a6/4/0

Thanks in advance,
Beems

Adding a condition on an *outer* table to the `Where` clause effectively turns an outer join into an inner join. Instead, add outer table conditions to their JOIN's ON clause, as @TimSchmelter shows below. — RBarryYoung, May 04 '15 at 15:11
@Beems: Here's a [good read](http://www.xaprb.com/blog/2009/04/08/the-dangerous-subtleties-of-left-join-and-count-in-sql/) for you... — Peter Schneider, May 04 '15 at 15:12
@PeterSchneider Not really. That article's just a lot of misrepresented alarmist ranting, that does contain buried in it the problem that the OP has but doesn't seem to understand what the actual cause of the problem is, which isn't because "people don't understand what LEFT JOIN does" as it states (nor is it the nature of NULL as it implies). In fact, in my experience, people who make this mistake generally understand LEFT JOINs pretty well, what they typically misunderstand is the difference between the WHERE clause and the ON clause. — RBarryYoung, May 04 '15 at 15:26

score 5 · Accepted Answer · edited May 23 '17 at 12:14

5

You could add it to the Join clause:

SELECT 
    f_assetnetbiosname, 
    COUNT(f_sessiondate) as 'Sessions'
FROM 
    tb_assets ass
LEFT OUTER JOIN 
    tb_sessions ses
  ON 
    ses.f_sessionnetbiosname = ass.f_assetnetbiosname 
    AND f_sessiondate BETWEEN '04/01/2015' AND '04/30/2015'
GROUP BY f_assetnetbiosname

Here's a demo: http://sqlfiddle.com/#!6/112a6/23/0

That's the difference of a Join-clause and a Where which is also mentioned here.

edited May 23 '17 at 12:14

Community

1
1

answered May 04 '15 at 15:07

Tim Schmelter

450,073
74
686
939

Thanks, that seems to work great. What is the difference between just a `LEFT JOIN` and a `LEFT OUTER JOIN` in this context? They both seem to return the same results. – Beems May 04 '15 at 15:13
1

@Beems: there's no difference. http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server Imo it's better to be explicit, otherwise you or your fellows always have to think about if it's an `OUTER JOIN`. – Tim Schmelter May 04 '15 at 15:14

SLin · Answer 2 · 2015-05-04T15:12:14.323

It sounds like you only need to count based on a date so you can put the condition into your count.

SELECT f_assetnetbiosname, 
COUNT(CASE WHEN (f_sessiondate BETWEEN '04/01/2015' AND '04/30/2015') 
           THEN 1 ELSE null
           END) as 'Sessions'
FROM tb_assets ass
LEFT JOIN tb_sessions ses ON ses.f_sessionnetbiosname = ass.f_assetnetbiosname  
GROUP BY f_assetnetbiosname

http://sqlfiddle.com/#!6/112a6/27

SQL JOIN not showing NULL results when WHERE clause used

2 Answers2