0

I don't know how does MySQL works internally, but I'm sure there are some problem with indexes or some metainformation about table counts:

mysql> select count(*) from Event;
+----------+
| count(*) |
+----------+
|     5925 |
+----------+
1 row in set (0,01 sec)

mysql> select count(*) from Event where event_id in (select discount_event_id from Discount);
+----------+
| count(*) |
+----------+
|     5901 |
+----------+
1 row in set (0,12 sec)

mysql> select count(*) from Event where event_id not in (select discount_event_id from Discount);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0,11 sec)

These 24 missing event_ids makes no sense. It's just logically impossible from my point of view. There cannot be 24 rows that both are and aren't in another set. Or they are, or they aren't.

Also, as suggested from some of the answers and comments, there are no NULL event_ids, since these are the rowids:

mysql> select count(*) from Event where event_id is null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

What is going on?

ABu
  • 10,423
  • 6
  • 52
  • 103
  • 2
    24 are those that are `null` – zerkms Jan 31 '19 at 23:54
  • https://stackoverflow.com/questions/129077/not-in-clause-and-null-values (though that's about SQL Server it's very likely to fit here too) – sticky bit Jan 31 '19 at 23:55
  • Try `SELECT COUNT(*), event_id FROM Event GROUP BY event_id WITH ROLLUP` to get a breakdown. – tadman Feb 01 '19 at 00:37
  • 1
    Looks like you're checking the wrong table/column for `NULL`s. Try: `SELECT discount_event_id FROM discount WHERE discount_event_id IS NULL;` – sticky bit Feb 01 '19 at 00:39
  • @stickybit Yeah, I just found the same. Write it as answer and I'll mark it as the valid one. – ABu Feb 01 '19 at 00:43

1 Answers1

4

It means there are 24 event_id that are NULL

select count(*) from Event where event_id IS NULL

both in and not in operators return NULL when you compare with NULL value, which is coerced to FALSE hence omitted from both result sets.

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • There'are no `NULL` `event_id`s since that field is the rowid of the `Event` table. See the update on the question. – ABu Feb 01 '19 at 00:28