Issue with IS NOT NULL in a join

• jeffshelix

  SSCrazy

  Points: 2251

  More actions

  January 18, 2018 at 3:50 pm

  #342383

  If I use this:

  LEFT JOIN
[TSI].[ZATS_BROKER_FEED] Z on L.Part_Num = Z.Composite_part and Z.[SUB_ORG] = 'FORD' and Z.created_date > Getdate() - 400

  I get 7700 rows back. Which is ok, but there is a 1 to many relationship with the left side to ZATS, so i get the universe of the part from ZATS, and it understand that, but i get rows where the column  UPDATE_DATE is populated or not.
  I don't need the rows  where Update_Date.is NULL

  So.... I tried this . . .

  LEFT JOIN
[TSI].[ZATS_BROKER_FEED] Z
on L.Part_Num = Z.Composite_part and Z.[SUB_ORG] = 'FORD' and Z.created_date > Getdate() - 400 and UPDATE_DATE IS NOT NULL.

  Now i get MORE rows! .. 
  Thoughts?  I know SQL treats NULLS differently, but i am not clear on when and how.

  Thanks!

• Lynn Pettis

  SSC Guru

  Points: 442447

  More actions

  January 18, 2018 at 4:31 pm

  #1976431

  jeffshelix - Thursday, January 18, 2018 3:50 PM

  If I use this:

  LEFT JOIN
[TSI].[ZATS_BROKER_FEED] Z on L.Part_Num = Z.Composite_part and Z.[SUB_ORG] = 'FORD' and Z.created_date > Getdate() - 400

  I get 7700 rows back. Which is ok, but there is a 1 to many relationship with the left side to ZATS, so i get the universe of the part from ZATS, and it understand that, but i get rows where the column  UPDATE_DATE is populated or not.
  I don't need the rows  where Update_Date.is NULL

  So.... I tried this . . .

  LEFT JOIN
[TSI].[ZATS_BROKER_FEED] Z
on L.Part_Num = Z.Composite_part and Z.[SUB_ORG] = 'FORD' and Z.created_date > Getdate() - 400 and UPDATE_DATE IS NOT NULL.

  Now i get MORE rows! .. 
  Thoughts?  I know SQL treats NULLS differently, but i am not clear on when and how.

  Thanks!

  Well, hard to say, what table is the column UPDATE_DATE in?  Can't tell from the snippet you provided especially since there is no table alais being used.

• jeffshelix

  SSCrazy

  Points: 2251

  More actions

  January 19, 2018 at 7:04 am

  #1976496

  Sorry about that lapse.  UPDATE_DATE is in the the ZATS Table (alias Z).
  ZATS as a CREATED DATE and an UPDATE DATE.  

  thanks

• jeffshelix

  SSCrazy

  Points: 2251

  More actions

  January 19, 2018 at 8:09 am

  #1976516

  You are correct, i had not aliased the column, so here is the new code ...

  LEFT JOIN
[TSI].[ZATS_BROKER_FEED] Z
on L.Part_Num = Z.Composite_part and Z.[SUB_ORG] = 'FORD' and Z.created_date > Getdate() - 400 and Z.UPDATE_DATE IS NOT NULL

   I still get about 3,000 MORE rows than i got with the Z.Update_date is not null section of code.

  thoughts? thanks

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 19, 2018 at 9:09 am

  #1976527

  jeffshelix - Friday, January 19, 2018 8:09 AM

  You are correct, i had not aliased the column, so here is the new code ...

  LEFT JOIN
[TSI].[ZATS_BROKER_FEED] Z
on L.Part_Num = Z.Composite_part and Z.[SUB_ORG] = 'FORD' and Z.created_date > Getdate() - 400 and Z.UPDATE_DATE IS NOT NULL

   I still get about 3,000 MORE rows than i got with the Z.Update_date is not null section of code.

  thoughts? thanks

  This is the opposite of what you said in your first post, where you state that you get more rows with UPDATE_DATE IS NOT NULL. You're confusing people (no you're not, you're confusing me).
  How about posting both queries with their rowcounts?
  

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden