直入正题
建表
CREATE TABLE user
(
id
BIGINT(20) NOT NULL AUTO_INCREMENT,
userName
VARCHAR(50) NULL DEFAULT NULL,
userPassword
VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (id
)
)
COLLATE=‘utf8_general_ci’
ENGINE=InnoDB
AUTO_INCREMENT=4
;
造数据
INSERT INTO user
(id
, userName
, userPassword
) VALUES (1, ‘张三’, ‘123’);
INSERT INTO user
(id
, userName
, userPassword
) VALUES (2, ‘张三’, ‘3’);
INSERT INTO user
(id
, userName
, userPassword
) VALUES (3, ‘李四’, NULL);
INSERT INTO user
(id
, userName
, userPassword
) VALUES (4, ‘李四’, NULL);
INSERT INTO user
(id
, userName
, userPassword
) VALUES (5, ‘aa’, ‘123’);
INSERT INTO user
(id
, userName
, userPassword
) VALUES (6, ‘aa’, NULL);
INSERT INTO user
(id
, userName
, userPassword
) VALUES (7, ‘bb’, NULL);
如下sql由于not in子查询结果有null,导致结果行空
## not in() 子查询结果不能有null,解决办法 加条件 t.userPassword is not null
select * from user t
where t.userPassword not in
(
select t.userPassword from user t where t.userName='aa'
)
子查询gropu by 导致结果集空。
如下:
select * from user t
where t.id not in
(
select t.id from user t
group by t.userName
)
如图select t.id from user t group by t.userName
子查询结果是4条数据,按理最终结果行是三条。
但是结果行是空,如下:
原因:子查询直接用gropu by,sql解析器直接获取结果集,并不会gropu by,所以子查询得到所有结果。主sql not in (所有结果),自然得到空。
通过再嵌套解决该问题,修改如下:
select * from user t
where t.id not in
(
select id
from (
select t.id from user t
group by t.userName
)tt
)
得到正确结果: