22,209
社区成员
发帖
与我相关
我的任务
分享
哪些人是员工(not exists)
select ename from emp_hiloo o
where not exists
(select 1 from emp_hiloo i
where o.empno = i.mgr)
哪些部门没有员工?(否定问题)
select dname from dept_hiloo o
where not exists
(select 1 from emp_hiloo i
where o.deptno = i.deptno)
对于not exsits来说,子查询的返回结果必须是no rows selected,才符合not exsits的条件(主表里的记录放入结果集).
如果子查询在执行过程中找到了一条符合条件的记录,子查询立即返回,not exists已经不可能成立了.(主表里的记录被过滤掉)
原来资料里找的希望对你有帮助
SELECT * FROM t1 a
WHERE EXISTS(SELECT 1 FROM t2 b WHERE a.id=b.id)
SELECT * FROM t1 a
WHERE NOT EXISTS(SELECT 1 FROM t2 b WHERE a.id=b.id)
SELECT * FROM t1 a
WHERE EXISTS(SELECT 1 FROM t2 b WHERE a.id<>b.id)
SELECT * FROM t1 a
WHERE NOT EXISTS(SELECT 1 FROM t2 b WHERE a.id<>b.id)
四个查询结果
id
-----------
1
2
(2 行受影响)
id
-----------
3
(1 行受影响)
id
-----------
1
2
3
(3 行受影响)
id
-----------
(0 行受影响)
CREATE TABLE t1(id int)
CREATE TABLE t2(id INT )
INSERT INTO t1
VALUES(1),(2),(3)
INSERT INTO t2
VALUES(1),(2)
SELECT * FROM t1 a
WHERE EXISTS(SELECT 1 FROM t2 b WHERE a.id=b.id)
SELECT * FROM t1 a
WHERE NOT EXISTS(SELECT 1 FROM t2 b WHERE a.id<>b.id)
/*
id
-----------
1
2
(2 row(s) affected)
id
-----------
*/
不懂,反正我这样理解都解释的通了,结贴啦啦啦[/quote]