SQLServer中in和not in子查询注意事项

最新推荐文章于 2024-08-20 14:34:43 发布

seteor

最新推荐文章于 2024-08-20 14:34:43 发布

阅读量2w

点赞数 2

分类专栏： SQLServer 文章标签： sqlserver null insert delete table 脚本

本文链接：https://blog.csdn.net/seteor/article/details/6678494

版权

SQLServer 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

当in子查询的结果集中包含null时，如： in('a','b',null)，父查询只返回非NULL结果集，因为null和null无法比较;

当子查询 not in的结果集中包含null时，父查询永远为空结果集;

当子查询中查询字段不存在时,SQL不会报错

select * from t1 where id in (select kk from t2) --t2中不存在kk字段,该语句不会报错,结果集为t1中所有行

如果是delete from t1 where id in (select kk from t2),呵呵,那就哭吧~~~

示例脚本：

CREATE TABLE T1

  (
   id INT,
   name VARCHAR(10)
  )

CREATE TABLE T2
  (
   id INT,
   address VARCHAR(10)
  )

INSERT INTO T1
SELECT 1, 'Tom'
UNION ALL
SELECT 2,  'Jack'
UNION ALL
SELECT 3,  'Lily'
UNION ALL
SELECT 4,  'Tony'

INSERT INTO T2
SELECT 1, 'Beijing'
UNION ALL
SELECT 2, 'Shanghai'
UNION ALL
SELECT NULL,'Tianjin'

SELECT * FROM T1
WHERE id IN (SELECT id FROM T2)

id name
1 Tom
2 Jack

*********************************************************************************
SELECT * FROM T1
WHERE id NOT IN (SELECT id FROM T2)

id name

*********************************************************************************
SELECT * FROM T1
WHERE id NOT IN (SELECT id FROM T2 WHERE id IS NOT NULL)

id name
3 Lily
4 Tony

*********************************************************************************

SELECT * FROM T1
WHERE name IN (SELECT name FROM T2)

id name
1 Tom
2 Jack
3 Lily
4 Tony

*********************************************************************************
DELETE FROM T1
WHERE name IN (SELECT name FROM T2)

(4 行受影响)