Post reply

NULLs in joins?

  • July 12, 2007 at 10:07 am

    #95231

    Good morning,

    I've got two tables that have a set of columns which could be null. For simplicity sake, let's call these...

    X.id, X.a, X.b, X.c and Y.id, Y.a, Y.b, Y.c

    Let's also assume that c COULD be NULL in either table.

    I need to find all the instances in X where a, b and c match a set of items returned from Y. I can easily enough query Y to get the values I want.

    select * from Y where id > 27

    And I'm using this as a subquery/nested query to select against X to get all the matching values in X.

    select * from X, (select * from Y where id > 27) Z where X.a = Z.a and X.b = Z.b and X.c = Z.c

    This always returns no results, even though there are perfectly valid results. If I take out the c = c portion (since C could be (and probably is) null), it works just fine. But, unfortunately, c is part of the 'fingerprint' I need to check for.

    So, what's up with SQL Server 2000? Why does NULL != NULL when doing a join? How can I get around this oddity? Or do I have to go through the hassle of doing this in two separate queries, getting a data-set where c IS null and another where c IS NOT null, then combining the results?

  • July 12, 2007 at 10:43 am

    #719039

    First of all, it is not an oddity.  Null does not equal anything, not even another null.

    Also, you should be using ANSI joins.

    To handle what you need, try this:

    SELECT * 
FROM X INNER JOIN Y 
ON x.a = y.a AND x.b = y.b AND (x.c = y.c OR (x.c IS NULL AND y.c IS NULL))
WHERE y.id > 27

    The specific answer to your question is to do (x.c = y.c OR (x.c IS NULL AND y.c IS NULL)) .  It may work in your version of the query to use this in place of just x.c=y.c clause.  The trick is to use the parenthesis to restrict the IS NULL matches to only if both values are Null, and combine with the OR on if valid non-null values match. 

    Hope this helps



    Mark

  • July 12, 2007 at 11:41 am

    #719056

    That did the job!

    Thank you very much!

    Frank

Viewing 3 posts - 1 through 2 (of 2 total)

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