Re: need help with query

A copy of this was sent to pascal <pbyrne_at_ie.oracle.com> (if that email address didn't require changing) On Tue, 15 Feb 2000 18:41:52 +0000, you wrote:

>Hello,
>
>I am trying to construct an efficient query for 8.1.5 that reads from a
>table using a composite key coming from two other tables and returns all
>rows that fail to match for both keys. For example all employees where
>(job_no, dept_no) from DEPT, JOBS are not in EMPLOYEE table.
>
>DEPT
>Dept_no NUMBER
>Dept_name VARCHAR2
>
>JOBS
>Job_no NUMBER
>Job_name VARCHAR2
>
>EMPLOYEE
>Job_no NUMBER
>Dept_no NUMBER
>Employee_no NUMBER
>...
>
>I have several approaches but they are all highly inefficient performing
>full table scans across all tables, ignoring the composite index on
>employee(job_no,dept_no), and using enormous amounts of temporary table
>space.
>

try:

select jobs.job_no, dept.dept_no
  from jobs, dept
   where NOT EXISTS ( select null

                        from employees
                       where employees.job_no = jobs.job_no
                         and employees.dept_no = dept.dept_no );

that'll cartesian product jobs with dept and then do an index probe into emp -- if one exists on job_no, dept_no -- and produce the report. You should start getting rows back right away (no temp space or anything)...

what you are asking to do is pretty hard -- make every JOB_NO/DEPT_NO combination and then if that combo does not exist in the EMPLOYEE table -keep it else discard it...

Your question and your attempts seem at odds with each other tho -- so maybe we need to back up a step as well. You say above:

"all employees where (job_no,dept_no) from DEPT, JOBS are not in the employee table". That sounds more like:

select * from employees
where not exists( select null from dept where dept.dept_no = employees.dept_no )   and not exists ( select null from jobs where jobs.job_no = employees.job_no )

> select jobs.job_no, dept.dept_no
> from jobs, dept
>minus
> select job_no, dept_no
> from employee
>
>and
>
> select jobs.job_no,dept.dept_no
> from jobs, dept
> where jobs.jobid||dept.dept_no not in (
> select job_no||dept_no from employee)
>
>
>If anyone know how to do this efficiently it would be much appreciated.

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation