Professional Web Applications Themes

weird results: (null!=null)?? - MySQL

I've got this select: select * from table1,table2 where table1.field1=table2.field1 and table1.field2=table2.field2 and it does not return any rows for the elements where both "field2" are null. the only way i can get those rows is using this query: select * from table1,table2 where table1.field1=table2.field1 and table1.field2=table2.field2 or (table1.field2 is null and table2.field2 is null) is this the normal behaviour? is there any other way in which those rows where both fields are null are returned? alex....

  1. #1

    Default weird results: (null!=null)??

    I've got this select:

    select * from table1,table2 where table1.field1=table2.field1 and
    table1.field2=table2.field2

    and it does not return any rows for the elements where both "field2" are
    null.

    the only way i can get those rows is using this query:

    select * from table1,table2 where table1.field1=table2.field1 and
    table1.field2=table2.field2 or (table1.field2 is null and table2.field2
    is null)


    is this the normal behaviour? is there any other way in which those rows
    where both fields are null are returned?

    alex.
    alex Guest

  2. #2

    Default Re: weird results: (null!=null)??

    En/na alex ha escrit: 

    ok, it seems this is a generic sql feature. so i'm going to use the "or"
    query. btw, the query was wrong as it needed a pair or parenthesis more:

    select * from table1,table2 where table1.field1=table2.field1 and
    (table1.field2=table2.field2 or (table1.field2 is null and table2.field2
    is null))
    alex Guest

  3. #3

    Default Re: weird results: (null!=null)??

    In article <4518fcd9$0$75040$sunsite.dk>, alex says... 

    In all of the various programming languages I've used so far that have
    null, it is valid that (null != null)

    --
    PleegWat
    Remove caps to reply
    PleegWat Guest

  4. #4

    Default Re: weird results: (null!=null)??

    PleegWat wrote:
     
    >
    > In all of the various programming languages I've used so far that have
    > null, it is valid that (null != null)[/ref]

    That's not exactly correct. In Java, for example, the comparison (null == null) equates to true. This applies to Progress also.

    I haven't tested any other languages, as these were the two that I have available.

    --

    Murdoc Guest

  5. #5

    Default Re: weird results: (null!=null)??

    Murdoc wrote: 
    >>
    >>In all of the various programming languages I've used so far that have
    >>null, it is valid that (null != null)[/ref]
    >
    >
    > That's not exactly correct. In Java, for example, the comparison (null == null) equates to true. This applies to Progress also.
    >
    > I haven't tested any other languages, as these were the two that I have available.
    >[/ref]

    C/C++ it's also true, but that's because null is actually just a special
    value (usually 0, but not necessarily). However, SQL defines any
    comparison to NULL as false.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  6. #6

    Default Re: weird results: (null!=null)??

    In SQL null is not a value, but it means actual value is not known for
    that column. Hence one unknown value can not be compared with other
    unknown value to say weather they are equal or not.So if you want to
    join on columns that can be null you have to take special care of using
    syntax like 'column is null' as you have done in the query below.

    alex wrote: 
    >
    > ok, it seems this is a generic sql feature. so i'm going to use the "or"
    > query. btw, the query was wrong as it needed a pair or parenthesis more:
    >
    > select * from table1,table2 where table1.field1=table2.field1 and
    > (table1.field2=table2.field2 or (table1.field2 is null and table2.field2
    > is null))[/ref]
    Meghana Guest

  7. #7

    Default Re: weird results: (null!=null)??

    On Tue, 26 Sep 2006 12:11:34 +0200, alex wrote: 

    This is normal. NULL cannot be compared to anything else, even another
    "value of NULL", because NULL does not have a value. Comparing NULL
    returns a NULL result as well, neither true nor false.

    --
    "The last refuge of the insomniac is a sense of superiority to the
    sleeping world."
    --Leonard Cohen, The Favourite Game
    Peter Guest

  8. #8

    Default Re: weird results: (null!=null)??

    Jerry Stuckle wrote:
     
    > >
    > >
    > > That's not exactly correct. In Java, for example, the comparison (null == null) equates to true. This applies to Progress also.
    > >
    > > I haven't tested any other languages, as these were the two that I have available.
    > >[/ref]
    >
    > C/C++ it's also true, but that's because null is actually just a special value (usually 0, but not necessarily). However, SQL defines any comparison to NULL as false.[/ref]

    That's interesting. Progress does a similar thing, that any arithmetic or boolean operation with a ? value returns ?, except for something like (? eq ?).

    --

    Murdoc Guest

Similar Threads

  1. Replies: 0
    Last Post: August 28th, 03:57 PM
  2. Replies: 0
    Last Post: August 28th, 03:57 PM
  3. #25292 [NEW]: is_null(1) and comparison with null produce different results
    By dizza at students dot ru in forum PHP Development
    Replies: 0
    Last Post: August 28th, 01:25 PM
  4. help. null dynamic table results in error
    By stewboy webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 2
    Last Post: July 22nd, 07:43 PM
  5. Error: ?null? is null or not an object
    By Murray *TMM* in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 10th, 06:36 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