作者prima (Underneath Your Clothes)
看板Database
标题[SQL ] SQL指令请教
时间Wed Jul 9 15:13:45 2008
最近在上database的课,上课有个SQL指令的意思有点摸不着头绪
能否有版友帮忙解释一下?
假设我有两个Table: EMP和DEPT
1.EMP(EMPNO, NAME, DNO, JOB, MGR, SAL)
PK是EMPNO, DNO是reference到EMP这个table的FK
2.DEPT(DNO, DNAMEM LOC)
PK是DNO
问题一:从DEPT这个table删除没有员工的部门的资料
(delete from the DEPT table the departments having no employees)
上课的slide答案是
delete DEPT
where ( select count(*)
from EMP
where DNO = DEPT.DNO) = 0
我有些困惑的是 假设在EMP这个table中,资料如下
EMPNO NAME DNO JOB
===========================
001 Lee A1 programmer
002 Chen A2 programmer
003 Wang A3 programmer
004 Chou A3 programmer
005 Lai A3 programmer
而DEPT此table则是
DNO DNAMEM LOC
==================
A1 test1 NY
A2 test2 OH
A3 test3 CA
B1 test4 NM
B2 test5 MI
B3 test6 LA
那( select count(*)
from EMP
where DNO = DEPT.DNO) 所得的count值应该是5
回到
delete DEPT
where ( select count(*)
from EMP
where DNO = DEPT.DNO) = 0
既然如此那这段delete是怎麽作用的,因为 5 !=0
困惑中...
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 164.107.92.125
1F：推 gargamel:我很好奇为何要用count(*)=0, 而不用not exists @_@ 07/09 18:21