Strange MySQL behavior, similar to SQL error

Question

I get a very strange behavior in MySQL that looks like some kind of strange error. I know that it’s just blaming a tried and tested tool for one mistake, but I went around this for a while.

I have 2 tables, I, with 2797 entries and C, from 1429. C links I.
I want to delete all entries in I that are not used by C, so I do:

select * from i where id not in (select id_i from c);

This returns 0 records, which, given the number of records in each table, is physically impossible. I am also sure that the query is right, because the same type of query that I used in the last 2 hours to clear other tables with lost records.

To make things even weirder ...

select * from i where id in (select id_i from c);

It works, and brings me 1297 entries, which I DO NOT want to delete.
So, IN works, but NOT IN does not.

Even worse:

select * from i where id not in (
  select i.id from i inner join c ON i.id = c.id_i
);

This works, although it should be equivalent to the first request (I'm just trying to figure it out at the moment).
Alas, I cannot use this query to delete, because I use the same table in which I am removed from the subquery.

I assume that something in my database is corrupted at the moment.

In case that matters, these are all MyISAM tables without any foreign keys, and I ran the same queries on my dev machine and on the production server with the same result, so any corruption could remain mysqldump / source, which sounds awfully weird.

, , , , /?

!
Daniel

Answer 1

, , NULL. IN NOT IN , , NULL. :

SQL, IN NULL , NULL, , - NULL.

NOT NULL - NULL, , .

:

CREATE TABLE c (id_i INT NULL);
INSERT INTO c (id_i) VALUES
(1),
(2),
(NULL);

CREATE TABLE i (id INT NOT NULL);
INSERT INTO i (id) VALUES
(1),
(2),
(3);

SELECT * FROM i WHERE id NOT IN (SELECT id_i FROM c); -- returns nothing
SELECT * FROM i WHERE id IN (SELECT id_i FROM c);     -- returns 1,2
SELECT * FROM i WHERE id NOT IN (                     -- returns 3
  SELECT i.id FROM i INNER JOIN c ON i.id = c.id_i
);

Answer 2

I, C

MySQL SQL multi-table DELETE, , NOT IN NULL .

DELETE I
FROM I LEFT OUTER JOIN C ON (i.id = c.id_i)
WHERE c.id_i IS NULL;

NB: , . , , , , .

Answer 3

What do you get if you try this query?

select * 
  from i 
 where NOT EXISTS (select id_i 
                     from c
                    where c.id_i = i.id
                  )