Professional Web Applications Themes

Joining on Null values - Microsoft SQL / MS SQL Server

Hello everyone Have a problem that I need help with I have a table Codes which looks like: ID, Code, modifier Any code can have multiple modifiers including a null modifier. I have another table which has a code, modifier and amount associated. When I try to join these tables on the codes and modifiers, it only picks the rows where the modifier is not null. Does anybody know how I can do this? Thanks Sameer...

  1. #1

    Default Joining on Null values

    Hello everyone
    Have a problem that I need help with
    I have a table Codes which looks like:
    ID, Code, modifier
    Any code can have multiple modifiers including a null
    modifier.
    I have another table which has a code, modifier and amount
    associated.
    When I try to join these tables on the codes and
    modifiers, it only picks the rows where the modifier is
    not null. Does anybody know how I can do this?
    Thanks
    Sameer
    Sameer Guest

  2. #2

    Default Re: Joining on Null values

    > where (
    > T1.Modifier = T2.Modifier
    > or T1.Modifier is NULL and T2.Modifier is NULL
    > )
    If you're going to use this syntax, I highly recommend using parens around
    the last two clauses, e.g.

    where (
    T1.Modifier = T2.Modifier
    or (T1.Modifier is NULL and T2.Modifier is NULL)
    )

    If you don't specify, you might have a different opinion than the engine,
    whether it should be like this:

    (T1.Modifier = T2.Modifier OR T1.Modifier IS NULL)
    AND
    (T2.Modifier IS NULL)

    or like this:

    (T1.Modifier = T2.Modifier)
    OR
    (T1.Modifier IS NULL AND T2.Modifier IS NULL)

    I've seen some pretty weird and unexpected results when using OR and AND in
    the same set of clauses, until I explicitly tell the engine which sets
    belong together.


    Aaron Bertrand - MVP Guest

  3. #3

    Default Re: Joining on Null values

    > I agree - parentheses are definitely recommended. But there is
    > a rule: AND is "stickier" than OR, so that
    >
    > where <predicate 1> and <predicate 2> or <predicate 3>
    >
    > should be interpreted as
    >
    > where (<predicate 1>and<predicate 2>) or <predicate 3>
    >
    > (See the "AND" article in Books Online.)
    > If the engine decides otherwise, it's a bug.
    That may be true, and I forget the cirstances where I've seen it. But as
    soon as you have more than one AND and one OR, it could quickly become
    confusing if you don't use decent separators...



    Aaron Bertrand - MVP Guest

Similar Threads

  1. WDDX and NULL values
    By agwillia in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: June 16th, 03:33 PM
  2. Please HELP! Problem with NULL values...
    By tacojohnellenich in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: June 11th, 06:55 PM
  3. Null Values
    By Aris Santillan in forum Dreamweaver AppDev
    Replies: 5
    Last Post: May 14th, 03:54 AM
  4. Detecting Null Values
    By Jordan in forum ASP.NET Building Controls
    Replies: 5
    Last Post: September 14th, 02:50 PM
  5. Null values enter to SQL
    By Movsum Zade in forum ASP.NET General
    Replies: 1
    Last Post: July 28th, 05:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139