|
当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 行受影响)
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
