comparison in the merge statement about null values

  • 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

  • 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/

  • 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

  • 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

  • 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.

  • 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/

  • 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

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

  • 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/

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

  • 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

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

  • 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

  • Ok. thanks.

  • 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.*

    )

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply