List:General Discussion« Previous MessageNext Message »
From:michael Date:March 13 2009 6:30pm
Subject:Re: avoiding use of Nulls (was: The <=> operator)
View as plain text  
> On Friday 13 March 2009 09:48:36 Michael wrote:
>> I'm sorry for the rant, but nulls in databases make me see red! NULLS
>> ARE GARBAGE!
>>
>> You are using a 'NULL-safe equal to operator', meaning, WHERE NOT
>> group_id  =  3, and oh, by the way, I DON'T WANT ANY NULLS TO QUALIFY AS
>> 3.
>>
>> And yes this will be much slower, because now we can't use the index on
>> group_id.
>>
>> Null is not an initial value, it is really saying that nothing has ever
>> been put into this field, item, or what-ever. It is uninitialized  and
>> no one can say exactly what is in it, this is why it is marked as null.
>>
>> Using the null-safe equal to operator seems wrong, of course too me
>> having any nulls in your data is wrong, or just plain dirty data, and I
>> wouldn't put any faith into the results from a database that contains
>> nulls, because the output is unpredictable if it is not very carefully
>> coded.
>>
>> You (everyone in the SQL world)  would be better off  using the IS NULL
>> and IS NOT NULL operators and the IFNULL() function to find all null
>> values in their data, and initialize them to the appropriate initial
>> value. This may require discussions with  the application designers or
>> project managers. Whom ever is responsible for these null values being
>> your data all the frecking time? It is people that don't  really
>> understand  what a null value is, and who also have the responsibility
>> of designing a database application. So people like me who write the
>> code need to add 'IS NOT NULL' to every conditional statement in our
>> logic.
>>
>> What you want to do <noted in your email below> should be very simple,
>> but only if you have "CLEAN DATA".  I would clean or what we call scrub
>> your data first, then you can execute a simple and very fast select
>> statement. Use a temporary table if you need to, just don't try to
>> process dirty data, the end results will only make you look bad to
>> anyone looking at the results.
>>
>> My $0.02,
>> Mike.
>>
>
> This is news to me, and I'd like to understand, so please don't take this
> as a
> flame or anything.
>
> I think I understand this part: When designing the database structure, you
> don't want just one table with a million columns, most of which are empty
> for
> any given row. What you do is break it up into several tables, with a
> relationship between the tables.  Thus the term "Relational database
> management system" (RDBMS)
> theoretically you break up your data into tables so that there is never a
> non-
> applicable field for any given row. In the real world, this can lead to a
> lot
> of complexity, so many people usually cheat and have a few "does not
> apply"
> fields for some records.
>
> Now, what I've been doing is using NULL for "does not apply" as it seems
> to be
> more true to the data model than using some sort of fake data.
> I freely admit that Nulls increase the complexity of the code, but isn't
> it
> justified?
>
> I would be interested in your explanation.
> Ray
>
>


I agree with most everything you said, as far as the use of a RDBMS, and
especially: quoting you,

"theoretically you break up your data into tables so that there is never a
non-applicable field for any given row."

Here is where I disagree with you, and please DON'T take this as an insult
(we're just haggling over good/bad practices ), but I think it is lazy
database design when you say:

"In the real world, this can lead to a lot of complexity, so many people
usually cheat and have a few "does not apply" fields for some records."

OK! Now here is my top 5 explanations why using Nulls is a bad idea:

Explanation(5): The more you understand how the database is to be used,
and the more complexity and thought you put into your database design, the
less complex it will be to retrieve reliable information out of it.
Furthermore, (and this is probably what makes me crazy when Nulls are
evolved) after a ten year stretch of software development, where I and a
team designed our own databases, I did a nine year stretch of statistical
programming, using databases designed by other people, and Nulls in the
data made the results unpredictable, and yeah, made me crazy! I had to
write nightly processes to resolve inconsistencies in the data, if at
least report inconsistencies. You know the old saying "Garbage in =
Garbage out", to me Nulls are garbage, and if there is a good reason for
nulls to be a part of good clean data then someone please help me
understand that.

Explanation(4):I think that the brilliant computer scientists who
developed the RDBMS database model, didn't want to support nulls
initially, but they had to because null is a form of data, it means "We
don't know what this is", and so it must be stored knowing that Null
should NOT be used as valid information.

Explanation(3):I think it was back in the sixties at Berkley, when some
smart guy on LSD found a use for the Null value, and now this use of Null
is taught in Universities all over the world, and considered to be a
blessed method to practice.

Explanation(2): Using a Null column, and you have an index on that column,
the index will not be used since NULL values do not get stored in an index
and that is why the optimizer will need to go to the data pages in order
to retrieve more information to suffice the requirements of the query.

Explanation(1): I'm just in a argumentative mood today.

Cheers and have a great weekend!
Mike.



Thread
The <=> operatorMorten13 Mar
  • Re: The <=> operatorMichael13 Mar
    • avoiding use of Nulls (was: The <=> operator)Ray13 Mar
      • Re: avoiding use of Nulls (was: The <=> operator)michael13 Mar
        • Re: avoiding use of Nulls (was: The <=> operator)Thomas Spahni13 Mar
          • Re: avoiding use of Nulls (was: The <=> operator)michael13 Mar
            • Re: avoiding use of NullsAndy Wallace13 Mar
              • Re: avoiding use of NullsArthur Fuller14 Mar
            • Re: avoiding use of NullsPJ13 Mar
            • Re: avoiding use of Nulls (was: The <=> operator)Claudio Nanni14 Mar
              • Re: avoiding use of Nulls (was: The <=> operator)Morten14 Mar
              • Re: avoiding use of Nulls (was: The <=> operator)michael14 Mar
                • Re: avoiding use of Nulls (was: The <=> operator)Mattia Merzi15 Mar
                • Re: avoiding use of Nulls (was: The <=> operator)Claudio Nanni15 Mar
                  • Re: avoiding use of Nulls (was: The <=> operator)Don Read15 Mar
                    • W2008 Server Issues?MCUSA)16 Mar
                      • Re: W2008 Server Issues?David M. Karr16 Mar
            • Re: avoiding use of Nulls (was: The <=> operator)Mattia Merzi14 Mar
        • Re: avoiding use of Nulls (was: The <=> operator)Ray14 Mar
          • Re: avoiding use of NullsAMichel Durand14 Mar
      • Getting single results per (left) record with INNER JOINNigel Peck13 Mar
        • Re: Getting single results per (left) record with INNER JOINNigel Peck13 Mar
          • Re: Getting single results per (left) record with INNER JOINArthur Fuller14 Mar
            • Re: Getting single results per (left) record with INNER JOINNigel Peck14 Mar
              • Re: Getting single results per (left) record with INNER JOINJohan De Meersman15 Mar
                • Re: Getting single results per (left) record with INNER JOINNigel Peck15 Mar
        • Re: Getting single results per (left) record with INNER JOINJoerg Bruehe16 Mar