MySQL “WHERE IN” produces proper result but “WHERE NOT IN” does not

Question

This is the table structure

CREATE TABLE `student_classlists` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `SessionCode` int(5) DEFAULT NULL,
  `CourseCode` varchar(10) DEFAULT NULL,
  `SectionCode` varchar(1) DEFAULT NULL,
  `LastName` varchar(255) DEFAULT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `StudentId` int(10) unsigned DEFAULT NULL,
  `FinalGradeSIS` int(10) DEFAULT NULL,
  `Status` varchar(10) DEFAULT NULL,
  `Faculty` varchar(255) DEFAULT '',
  `Email` varchar(255) DEFAULT NULL,
  `ClassListDate` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=576 DEFAULT CHARSET=utf8;

The Status column is largely composed of NULL rows, with just a few exceptions. I am trying to ignore those records that are not null. The following query does not work:

SELECT StudentId FROM student_classlists WHERE `Status` NOT IN ('Drop') GROUP BY StudentId

For some reason it produces an unexpected and empty result. But a very similar query with the only exception of dropping the NOT produces an expected result

SELECT StudentId FROM student_classlists WHERE `Status` IN ('Drop') GROUP BY StudentId

What's happening here?

Another victim of three-valued logic. Check out http://stackoverflow.com/questions/129077/not-in-constraint-and-null-values for the reverse. — Paul Draper, Nov 03 '13 at 01:54

score 1 · Accepted Answer · answered Nov 03 '13 at 02:04

1

Rows with NULL status will not appear in the output, because they are neither inside nor outside the IN list. To get these rows, add COALESCE, like this:

SELECT StudentId
FROM student_classlists
WHERE COALESCE(`Status` NOT IN ('Drop'), 1)
GROUP BY StudentId

This effectively says that records with NULL value in the Status column must be included.

answered Nov 03 '13 at 02:04

Sergey Kalinichenko

714,442
84
1,110
1,523

Great - I had the feeling there was something crucial I did not know about MySQL... Thanks! – Levi Nov 03 '13 at 03:58

score 0 · Answer 2 · answered Nov 03 '13 at 01:53

0

If you want only NULL Status records, you could do:

SELECT StudentId FROM student_classlists WHERE Status IS NULL

answered Nov 03 '13 at 01:53

Jack B

139
7

MySQL “WHERE IN” produces proper result but “WHERE NOT IN” does not

2 Answers2