I have a requirement as below :
Would relate to the emp and the dept table of the SCOTT schema.
Suppose there is a third table dept_lookup .
create table dept_lookup
(
deptno number,-----deptno references dept's deptno
emp_count
)
insert into dept_lookup values (10,100);
insert into dept_lookup values (20,200);
*note : not all department's count exist in this table
in the join of emp and the dept table , i have to derive an extra column in which i have to check whether a particular deptno exists in the dept_lookup table .If exists populate the emp_count is there then populate the count else populate a default count of 0 .I may not have pictured my actual scenario clearly but this is the extract . I want to use a scalar subquery for this .
something like
case when (select count(1) from dept_lookup dl where d.depno=dl.deptno)>=1
then dl.emp_count else 0 end
or
case when (select 1 from dept_lookup dl where exists (select 1 from dept d where d.deptno=dl.deptno)=1
then dl.emp_count else 0 end
This has to be implemented in very large tables , considering the fact that indexes are built on the deptno on each of these tables . What would be the performance impact if i compare exists with count(1). Which one is better ?
Thanks
Rahul