SQL2005 NOT IN

Question

SQL2005的NOT IN 函数表A中一字段　USERNAME VARCHAR(20)
其中有值
LUCY
JACK
NULL我在查询时，使用了NOT IN
则出现疑问SELECT USERNAME FORM 表Ａ
WHERE USERNAME NOT IN ('LUCY','JACK')则出现

Answer 1

--1. NULL 对 IN（NOT IN） 查询的影响
--测试数据
DECLARE @1 TABLE(col1 int)
INSERT @1 SELECT 1
UNION ALL SELECT NULL
UNION ALL SELECT 2DECLARE @2 TABLE(col1 int)
INSERT @2 SELECT 1--查询
SELECT [@1总记录数]=COUNT(*) FROM @1
--结果: 3SELECT [@1在@2表中存在的记录数]=COUNT(*) FROM @1 a
WHERE col1 IN(SELECT col1 FROM @2)
--结果: 1SELECT [@1在@2表中存在的记录数]=COUNT(*) FROM @1 a
WHERE col1 NOT IN(SELECT col1 FROM @2)
--结果: 1--在@2中插入一条NULL值
INSERT @2 SELECT NULL
SELECT [@1在@2表中存在的记录数]=COUNT(*) FROM @1 a
WHERE col1 IN(SELECT col1 FROM @2)
--结果: 1SELECT [@1在@2表中存在的记录数]=COUNT(*) FROM @1 a
WHERE col1 NOT IN(SELECT col1 FROM @2)
--结果: 0
GO
--2. 使用 EXISTS 代替IN
--测试数据
DECLARE @1 TABLE(col1 int)
INSERT @1 SELECT 1
UNION ALL SELECT NULL
UNION ALL SELECT 2DECLARE @2 TABLE(col1 int)
INSERT @2 SELECT 1
UNION ALL SELECT NULLSELECT [@1在@2表中存在的记录数]=COUNT(*) 
FROM @1 a
WHERE EXISTS(SELECT * FROM @2 WHERE col1=a.col1)
--结果: 1SELECT [@1在@2表中存在的记录数]=COUNT(*) 
FROM @1 a
WHERE NOT EXISTS(SELECT * FROM @2 WHERE col1=a.col1)
--结果: 2

Answer 2

USERNAME
--------
NULL(1 行受影响)

Answer 3

--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([username] nvarchar(4))
INSERT [tb]
SELECT N'LUCY' UNION ALL
SELECT N'JACK' UNION ALL
SELECT null
GO
--SELECT * FROM [tb]-->SQL查询如下:
SELECT USERNAME FROM tb 
WHERE USERNAME NOT IN ('LUCY','JACK') 
/*
USERNAME
--------(0 行受影响)
*/
--1.你的FROM写错
--2.IN,LIKE等后面若跟字符,也就默认设置了返回记录为字符型,所以NULL值不会返回.具体原因有待研究.
--你可以用如下的查询方式,选出带NULL值的记录
SELECT USERNAME FROM tb t
WHERE NOT EXISTS(
SELECT 1 FROM tb 
WHERE USERNAME=t.USERNAME 
AND USERNAME IN ('LUCY','JACK'))
/*
USERNAME
--------
NULL(1 行受影响)
*/我這個寫了有一小會兒了，不貼出來也過意不去。

Answer 4

结帖太快了，没能看到大师的方法
IN,LIKE等后面若跟字符,也就默认设置了返回记录为字符型,所以NULL值不会返回.具体原因有待研究.