List:General Discussion« Previous MessageNext Message »
From:Ray Date:March 13 2009 5:11pm
Subject: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

 

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