Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


comparison in the merge statement about null values


comparison in the merge statement about null values

Author
Message
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2479 Visits: 3867
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
Sean Lange
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18446 Visits: 17190
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2479 Visits: 3867
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
Evil Kraig F
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6337 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2479 Visits: 3867
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
Sean Lange
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18446 Visits: 17190
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6337 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2479 Visits: 3867
I think Greg meant not sargable is because I use Isnull function on the left of the comparison.
Sean Lange
Sean Lange
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18446 Visits: 17190
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2479 Visits: 3867
Thanks, so I still cannot get a better improved solution?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search