Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

comparison in the merge statement about null values Expand / Collapse
Author
Message
Posted Wednesday, August 22, 2012 1:22 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, November 21, 2014 5:22 PM
Points: 1,790, Visits: 3,252
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
Post #1348679
Posted Wednesday, August 22, 2012 1:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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)
Post #1348683
Posted Wednesday, August 22, 2012 1:53 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, November 21, 2014 5:22 PM
Points: 1,790, Visits: 3,252
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
Post #1348698
Posted Wednesday, August 22, 2012 2:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:51 AM
Points: 5,446, Visits: 7,616
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
Post #1348710
Posted Wednesday, August 22, 2012 2:46 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, November 21, 2014 5:22 PM
Points: 1,790, Visits: 3,252
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.
Post #1348744
Posted Wednesday, August 22, 2012 2:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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.


_______________________________________________________________

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)
Post #1348747
Posted Wednesday, August 22, 2012 2:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:51 AM
Points: 5,446, Visits: 7,616
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
Post #1348754
Posted Wednesday, August 22, 2012 2:59 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, November 21, 2014 5:22 PM
Points: 1,790, Visits: 3,252
I think Greg meant not sargable is because I use Isnull function on the left of the comparison.
Post #1348757
Posted Wednesday, August 22, 2012 3:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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)
Post #1348762
Posted Wednesday, August 22, 2012 3:34 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, November 21, 2014 5:22 PM
Points: 1,790, Visits: 3,252
Thanks, so I still cannot get a better improved solution?
Post #1348777
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse