Can JOIN be used as alternative to NOT EXIST

[hema118]

Hi..

Have a tricky question here. Your help is appreciated !

I have 3 tables as below

TABLE A

DEPT_NBR MGT_NBR MGR_NAME
1 111 ABD
6 222 XXX
2 33 RRR
4 444 JJJJ
3 555 DDDD
7 77 NNNN

TABLE B

EMP_NBR EMP_NAME DEPT_NBR MGR_NBR
1 DON 1 111
2 HARI 5 33
3 RAMESH 3 555
4 JOE 2 33
5 DENNIS 2 33
6 NIMISH 4 444


TABLE C

DEPT_NBR DEPT_NAME MGR_NBR
1 FINANCE 111
2 HR 33
3 ADMIN 555
4 PROJECT 444
5 WFM 33
7 PMO 77

My requirement is to extract the rows in Table A which has no overlaps in both Tables B and C.

I have the used NOT EXISTS for the extraction

SELECT MGR_NAME
FROM TABLE A
WHERE NOT EXISTS
( SELECT DEPT_NBR,MGR_NB R FROM TABLE B )
AND NOT EXISTS
( SELECT DEPT_NBR,MGR_NB R FROM TABLE C )

Q1 ) Would like to know if there is an option to use JOIN instead of NOT
EXISTS and the extract he same output in a single query.

Q2) Can JOIN command be used ON multiple columns. For example, in this case is there a syntax to join on both Dept no and Manager no ? I need both to be used in the JOIN option and not one of them in the WHERE clause.

[rahulj]

Ohh yes.... I think it should be possible...but first and foremost can you please mention why do you have such a requirement (i.e using JOIN instead of NOT EXISTS). Your solution definitely is more readable.

[cburnett]

Yes, it is possible. I have found that this join technique often performs better than the correlated NOT EXISTS. (Visual Explain says this is the case even for this small sample). Not Exists query:

select a.* 
              from a  
             where not exists(select 1
                                         from b
                                       where b.dept_nbr = a.dept_nbr
                                          and b.mgr_nbr  = a.mgr_nbr) 
                 and not exists(select 1
                                         from c
                                       where c.dept_nbr = a.dept_nbr
                                          and c.mgr_nbr  = a.mgr_nbr)

Join query:

select a.*
              from a
              left outer join b
                on b.dept_nbr = a.dept_nbr
              and b.mgr_nbr  = a.mgr_nbr
              left outer join c
               on c.dept_nbr = a.dept_nbr
              and c.mgr_nbr = a.mgr_nbr
            where b.dept_nbr is null
               and c.dept_nbr is null