三个表 comp comp_id pk comp_name depar dept_id pk comp_id fk dept_name employee e_id pk dept_id fk e_name e_level number e_hiredate date求:员工姓名,编码 满足:部门是财务部 公司名称是csdn 从2000-01-01 到 2003-01-01 Select e_id, e_name From Employee Where dept_id in (Select depar.dept_id From Comp,Depar Where Comp.id = Depar.id AND Depar.dept_name='财务部' AND Comp.comp_name = 'csdn') AND Employee.e_hiredate between to_date('2000-01-01','yyyy-mm-dd') and to_date('2003-01-01','yyyy-mm-dd');
三个表 comp comp_id pk comp_name depar dept_id pk comp_id fk dept_name employee e_id pk dept_id fk e_name e_level number e_hiredate date求:员工姓名,编码 满足:部门是财务部 公司名称是csdn 从2000-01-01 到 2003-01-01 Select e_id, e_name From Employee Where dept_id in (Select depar.dept_id From Comp,Depar Where Comp.id = Depar.id AND Depar.dept_name='财务部' AND Comp.comp_name = 'csdn') AND Employee.e_hiredate between to_date('2000-01-01','yyyy-mm-dd') and to_date('2003-01-01','yyyy-mm-dd'); 那我这样写对吗?效率根LEFT JION那个高啊
left outer join 最少保证主表(位于left outer join左边的表)的信息能够检索出来,即只要主表不是空记录,那么结果肯定不是空记录;而where in只是检索条件之一,可能出现空记录.
不能用where in代替
comp
comp_id pk
comp_name depar
dept_id pk
comp_id fk
dept_name
employee
e_id pk
dept_id fk
e_name
e_level number
e_hiredate date求:员工姓名,编码
满足:部门是财务部
公司名称是csdn
从2000-01-01 到 2003-01-01
Select e_id, e_name
From Employee
Where dept_id in
(Select depar.dept_id
From Comp,Depar
Where Comp.id = Depar.id AND
Depar.dept_name='财务部' AND
Comp.comp_name = 'csdn') AND
Employee.e_hiredate between to_date('2000-01-01','yyyy-mm-dd') and to_date('2003-01-01','yyyy-mm-dd');
星星的回答其次
我是觉得这个题目可以这样写所以我想问问
不用left jion 用where in页可以
我就想知道可不可以替代啊~~~
这么简单的问题劳烦这么多高手拉
55
三个表
comp
comp_id pk
comp_name depar
dept_id pk
comp_id fk
dept_name
employee
e_id pk
dept_id fk
e_name
e_level number
e_hiredate date求:员工姓名,编码
满足:部门是财务部
公司名称是csdn
从2000-01-01 到 2003-01-01
Select e_id, e_name
From Employee
Where dept_id in
(Select depar.dept_id
From Comp,Depar
Where Comp.id = Depar.id AND
Depar.dept_name='财务部' AND
Comp.comp_name = 'csdn') AND
Employee.e_hiredate between to_date('2000-01-01','yyyy-mm-dd') and to_date('2003-01-01','yyyy-mm-dd'); 那我这样写对吗?效率根LEFT JION那个高啊