mysql - 使用 NOT IN 从这个查询中我能得到什么？

Question

如果您尝试运行以下查询，结果是什么？假设对于此查询，所有书籍都有 publ_id 值，并且没有 publ_id 为 90

Select *  
From a_bkinfo.books 
Where publ_id not in (90, null);

我运行查询并没有收到任何行，但我认为这会返回 publ_id 不是 90 的行。我很困惑。我没看到什么？

Table:
-- create publishers
create table a_bkinfo.publishers (
   publ_id           integer          not null
 , publ_name         varchar(25)      not null  
 , constraint bk_publishers_pk        primary key(publ_id) 
 , constraint publ_id_range check (publ_id >1000)
)engine = INNODB;

Inserts:
-- publishers
Insert into a_bkinfo.publishers values (9000, 'Microsoft Press') ;
Insert into a_bkinfo.publishers values (9456, 'New Directions') ;
Insert into a_bkinfo.publishers values (9102, 'Alfred A. Knopf') ;
Insert into a_bkinfo.publishers values (9325, 'Addison Wesley') ;
Insert into a_bkinfo.publishers values (9745, 'Morgan Kaufmann') ;
Insert into a_bkinfo.publishers values (9521, 'Benjamin/Cummings') ;
Insert into a_bkinfo.publishers values (9822, 'O''Reilly') ; 
Insert into a_bkinfo.publishers values (9030, 'McGraw Hill') ;
Insert into a_bkinfo.publishers values (9444, 'APress') ;
Insert into a_bkinfo.publishers values (9528, 'Manning');
Insert into a_bkinfo.publishers values (9020, 'Princeton Univer Press') ;
Insert into a_bkinfo.publishers values (9021, 'Yale University Press') ;
Insert into a_bkinfo.publishers values (9022, 'Havard University Press') ;
Insert into a_bkinfo.publishers values (9507, 'J.Q. Vanderbildt');
Insert into a_bkinfo.publishers values (9664, 'WROX') ;
Insert into a_bkinfo.publishers values (9825, 'MySQL Press') ;
Insert into a_bkinfo.publishers values (9623, 'Prentice Hall') ;
Insert into a_bkinfo.publishers values (9725, 'Springer') ;
Insert into a_bkinfo.publishers values (9561, 'Houghton Mifflin'); 
Insert into a_bkinfo.publishers values (9902, 'W.W. Norton ') ;
Insert into a_bkinfo.publishers values (9023, 'Holt Paperbacks') ;
Insert into a_bkinfo.publishers values (9024, 'Univ of California Press') ;
Insert into a_bkinfo.publishers values (9776, 'Simon and Schuster') ;

Answer 1

它将返回一个空集。

90 不在你的值(value)观部分的任何地方......

Select * From publishers Where publ_id not like '90%';

这将找到所有以 90 开头且后面有任意数字组合的数字。

通过添加模数符号并抛出单引号，您要查找的字符串表示形式

9 0_ _

更新

很抱歉，我的初始子句正在查找 publ_id WHERE NOT IN(例如“9038”、“9039”)。否则，当您搜索或比较数字或字母字符串时，必须使用 LIKE。