comparison in the merge statement about null values

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  August 22, 2012 at 1:22 pm

  #265655

  I use the merge statement in a sproc to insert, update and delete records from a staging table to a production table.

  In the long sql, here is a part of it,

  When Matched and

  ((Student.SchoolID <> esis.SchoolID

  OR

  Student.GradeLevel <> esis.GradeLevel

  OR

  Student.LegalName <> esis.LegalName

  OR

  Student.WithdrawDate <> esis.WithdrawDate

  Student.SPEDFlag <> esis.SPEDFlag

  OR

  Student.MailingAddress <> esis.MailingAddress)

  Then update

  Set Student.Schoolid=esis.schoolid,

  .....

  My question is how about if the column has null values in it.

  for example

  if schoolID is null in production table is null, but in staging table is not null, will the <> return true.

  or if either side of <> has a null value, will it return true.

  I don't want it to omit some records and causing the students records not get updated.

  If not return true, how to fix this?

  THanks

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  August 22, 2012 at 1:28 pm

  #1528881

  sqlfriends (8/22/2012)

  ---

  I use the merge statement in a sproc to insert, update and delete records from a staging table to a production table.

  In the long sql, here is a part of it,

  When Matched and

  ((Student.SchoolID <> esis.SchoolID

  OR

  Student.GradeLevel <> esis.GradeLevel

  OR

  Student.LegalName <> esis.LegalName

  OR

  Student.WithdrawDate <> esis.WithdrawDate

  Student.SPEDFlag <> esis.SPEDFlag

  OR

  Student.MailingAddress <> esis.MailingAddress)

  Then update

  Set Student.Schoolid=esis.schoolid,

  .....

  My question is how about if the column has null values in it.

  for example

  if schoolID is null in production table is null, but in staging table is not null, will the <> return true.

  or if either side of <> has a null value, will it return true.

  I don't want it to omit some records and causing the students records not get updated.

  If not return true, how to fix this?

  THanks

  No the NULLs will not evaluate to true. You would need to include some additional OR conditions.

  OR Student.SchoolID is null

  OR ...

  Anytime you have a NULL on either side of an (in)equality check NULL will not be returned.

  select *

  from sys.objects

  where 1 <> null

  or null <> 1

  or null <> null

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  August 22, 2012 at 1:53 pm

  #1528894

  Because I have quite a few <> comparisons in my sql, ( in my post it is just a part of them), if I write something like :

  Student.WithdrawDate <> esis.WithdrawDate

  OR (student.WithdrawDate is NULL AND esis.WithdrawDate is NOT NULL)

  OR (student.WithdrawDate is NOT NULL AND esis.withdrawdate is NULL)

  It will get very long, is it an easier way to do it?

  can I use isnull(student.mailingaddress,'')<>isnull(esis.mailingaddress,'')

  Thanks

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  August 22, 2012 at 2:05 pm

  #1528903

  sqlfriends (8/22/2012)

  ---

  can I use isnull(student.mailingaddress,'')<>isnull(esis.mailingaddress,'')

  Yep, that's the usual workaround, just realize the entire query will be non-SARGable.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  August 22, 2012 at 2:46 pm

  #1528934

  Evil Kraig F (8/22/2012)

  ---

  sqlfriends (8/22/2012)

  ---

  can I use isnull(student.mailingaddress,'')<>isnull(esis.mailingaddress,'')

  Yep, that's the usual workaround, just realize the entire query will be non-SARGable.

  I would like my query to be sargable.

  So what other good approach to do the comparison but still keep null compare valid?

  Thanks.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  August 22, 2012 at 2:48 pm

  #1528937

  sqlfriends (8/22/2012)

  ---

  Evil Kraig F (8/22/2012)

  ---

  sqlfriends (8/22/2012)

  ---

  can I use isnull(student.mailingaddress,'')<>isnull(esis.mailingaddress,'')

  Yep, that's the usual workaround, just realize the entire query will be non-SARGable.

  I would like my query to be sargable.

  So what other good approach to do the comparison but still keep null compare valid?

  Thanks.

  Your big long list you started above. :w00t:

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  August 22, 2012 at 2:58 pm

  #1528944

  Edit: Damned quote bug...

  sqlfriends (8/22/2012)

  ---

  Evil Kraig F (8/22/2012)

  ---

  sqlfriends (8/22/2012)

  ---

  can I use isnull(student.mailingaddress,'')<>isnull(esis.mailingaddress,'')

  Yep, that's the usual workaround, just realize the entire query will be non-SARGable.

  I would like my query to be sargable.

  So what other good approach to do the comparison but still keep null compare valid?

  Thanks.

  I should rephrase that statement. The query MAY be sargeable, but none of these compares will be. They'll always end up as post-lookup predicates (there's an official name out there for that which I don't know). There's no good solution, really, when dealing with NULLS like this. Either you build out the where clause of doom, or you use coding shortcuts that hopefully won't hurt too badly. Avoid using this on joins like the plague, but after a certain point you just go with it.

  I've got a query that uses about 70 of those in a reporting environment right now. It SARGs on the non-compare fields (and indexes I've built specifically for that) and then the compare happens after the rows are memory-joined. It'll seek when appropriate, but you'll have to dig into the query and make sure everything outside of the compare is lined up.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  August 22, 2012 at 2:59 pm

  #1528947

  I think Greg meant not sargable is because I use Isnull function on the left of the comparison.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  August 22, 2012 at 3:02 pm

  #1528952

  sqlfriends (8/22/2012)

  ---

  I think Greg meant not sargable is because I use Isnull function on the left of the comparison.

  It doesn't matter which side, it is going to force a scan when using ISNULL in the where clause.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  August 22, 2012 at 3:34 pm

  #1528965

  Thanks, so I still cannot get a better improved solution?

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  August 22, 2012 at 3:45 pm

  #1528968

  sqlfriends (8/22/2012)

  ---

  Thanks, so I still cannot get a better improved solution?

  Without query, underlying schema, and sqlplan? Nope. You've gone from general methodology to a particular solution's optimization. The general mechanism is sound, the implementation can hang you up. Need to see everything you're working with to make a more effective recommendation.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  August 22, 2012 at 3:49 pm

  #1528971

  If I simplize my question, any alternative to use <> to compare columns with null values?

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  August 22, 2012 at 3:59 pm

  #1528975

  sqlfriends (8/22/2012)

  ---

  If I simplize my question, any alternative to use <> to compare columns with null values?

  Nope, compare them in a non-null version (using ISNULL), or build the incredibly painful where clause you initially described. Anything else is worse.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  August 22, 2012 at 4:03 pm

  #1528976

  Ok. thanks.

• Paul White

  SSC Guru

  Points: 150467

  More actions

  August 22, 2012 at 4:31 pm

  #1528986

  sqlfriends (8/22/2012)

  ---

  Because I have quite a few <> comparisons in my sql, ( in my post it is just a part of them), if I write something like :

  Student.WithdrawDate <> esis.WithdrawDate

  OR (student.WithdrawDate is NULL AND esis.WithdrawDate is NOT NULL)

  OR (student.WithdrawDate is NOT NULL AND esis.withdrawdate is NULL)

  It will get very long, is it an easier way to do it?

  can I use isnull(student.mailingaddress,'')<>isnull(esis.mailingaddress,'')

  There is a better, and easier way to do it using NOT EXISTS and INTERSECT. I describe the technique here: http://bit.ly/EqualityComparison

  This handles NULLs correctly, means you don't have to find a 'magic value' to use with ISNULL or COALESCE, and is SARGable. The basic pattern is as follows, but please read the full blog post to make sure you understand how and why it works.

  WHERE NOT EXISTS

  (

  SELECT

  Student.SchoolID,

  Student.GradeLevel,

  Student.LegalName,

  Student.WithdrawDate,

  Student.SPEDFlag,

  Student.MailingAddress

  INTERSECT

  SELECT

  esis.SchoolID,

  esis.GradeLevel,

  esis.LegalName,

  esis.WithdrawDate,

  esis.SPEDFlag,

  esis.MailingAddress

  )

  Listing the columns is easy in SSMS (drag them from the object explorer to the query pane). In the case where all columns are significant, you can also use the star syntax:

  WHERE NOT EXISTS

  (

  SELECT

  Student.*

  INTERSECT

  SELECT

  esis.*

  )

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi