List:General Discussion« Previous MessageNext Message »
From:Michael Date:March 13 2009 3:48pm
Subject:Re: The <=> operator
View as plain text  
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.

Morten wrote:
>
> Hi,
>
> I have a query where I want to retrieve all the people who are not in 
> a specific group. Like so:
>
>     SELECT id, name FROM people WHERE group_id != 3;
>
> Since people may not be in a group at all, I also need to test if the 
> column is NULL:
>
>     SELECT id, name FROM people WHERE group_id != 3 OR group_id IS NULL;
>
> Running that through EXPLAIN things look fine, but if I instead use:
>
>     SELECT id, name FROM people WHERE NOT group_id <=> 3;
>
> I get a full table scan. Is that because is <=> equivalent to a 
> function in a sense?
>
> Thanks.
>
>
>

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