在Oracle中,空字符串‘’和NULL等同
在MySQL中,空字符串‘’和NULL不同
MySQL中,NOT IN作用范围不包含NULL,但包含''
Oracle中,因为‘’和NULL一样,所以NOT IN作用范围只有不包含NULL
测试数据:
表test1
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test_db` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test_db`; /*Table structure for table `test1` */
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` ( `id` INT(10) NOT NULL, `score` INT(20) DEFAULT NULL, `class` CHAR(10) COLLATE utf8_bin DEFAULT NULL, `name` CHAR(20) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `test1` */
INSERT INTO `test1`(`id`,`score`,`class`,`name`) VALUES (1,100,'语文','张三'),(2,98,'语文','李四'),(3,98,'语文','王五'),(4,98,'数学','张三'),(5,96,'数学','李四'),(6,92,'数学','王五'),(7,85,'数学','张三'),(8,96,'语文','张三'),(9,96,'语文','张三'),(10,91,'语文','张三'),(11,91,NULL,'张三');
表test2
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test_db` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test_db`; /*Table structure for table `test2` */
DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` ( `id` INT(10) NOT NULL, `score` INT(20) DEFAULT NULL, `class` CHAR(10) COLLATE utf8_bin DEFAULT NULL, `name` CHAR(20) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `test2` */
INSERT INTO `test2`(`id`,`score`,`class`,`name`) VALUES (1,100,'语文','张三'),(2,98,'语文','李四'),(3,98,'语文','王五'),(4,98,'数学','张三'),(5,96,'数学','李四'),(6,92,'数学','王五'),(7,85,'数学','张三'),(8,96,'语文','张三'),(9,96,'语文','张三'),(10,91,'语文','张三'),(11,91,'','张三');
SELECT * FROM test_db.test1 t WHERE t.class NOT IN ('语文');
SELECT * FROM test_db.test2 t WHERE t.class NOT IN ('语文');