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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
