SQLServer中IN产生的BUG

USE tempdb
GO
IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
IF OBJECT_ID('t2') IS NOT NULL DROP TABLE t2
GO
CREATE TABLE t1(id INT)
CREATE TABLE t2(id INT)
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (2)
INSERT INTO t1 VALUES (3)
--INSERT INTO t1 VALUES (NULL)
GO
INSERT INTO t2 VALUES (1)
INSERT INTO t2 VALUES (2)
INSERT INTO t2 VALUES (4)
INSERT INTO t2 VALUES (NULL)
GO
--t1 表中无NULL 值，可正常显示。有NULL, NULL 也出不来
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2)
/*
id
1
2
*/
--t1 表中有无NULL 值，都显示不了。t2无NULL值才可正常显示
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2)
/*
id
*/
INSERT INTO t1 VALUES (NULL)
SELECT * FROM t1 WHERE EXISTS(
	SELECT 1 FROM t2 WHERE t1.id=t2.id	
)
/*
id
1
2
*/
SELECT * FROM t1 WHERE NOT EXISTS (
	SELECT * FROM t2 WHERE t1.id=t2.id	
)
/*
id
3
NULL
*/
SELECT * FROM t1 WHERE t1.id NOT in (
	SELECT t2.id FROM t2 WHERE t2.id IS NOT NULL	
)
/*******************************************
 * 结论：
 * not in 在子查询中有NULL记录时，获取不到任何记录
 * 此时可以用 EXISTS 代替， 或子查询中强制只取 NOT NULL记录
 *******************************************/