By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,130 Members | 2,100 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,130 IT Pros & Developers. It's quick & easy.

Can JOIN be used as alternative to NOT EXIST

P: 1
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_NBR FROM TABLE B )
AND NOT EXISTS
( SELECT DEPT_NBR,MGR_NBR 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.
Sep 17 '08 #1
Share this Question
Share on Google+
2 Replies


P: 4
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.
Sep 17 '08 #2

P: 57
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:

Expand|Select|Wrap|Line Numbers
  1. select a.* 
  2.               from a  
  3.              where not exists(select 1
  4.                                          from b
  5.                                        where b.dept_nbr = a.dept_nbr
  6.                                           and b.mgr_nbr  = a.mgr_nbr) 
  7.                  and not exists(select 1
  8.                                          from c
  9.                                        where c.dept_nbr = a.dept_nbr
  10.                                           and c.mgr_nbr  = a.mgr_nbr) 
Join query:

Expand|Select|Wrap|Line Numbers
  1. select a.*
  2.               from a
  3.               left outer join b
  4.                 on b.dept_nbr = a.dept_nbr
  5.               and b.mgr_nbr  = a.mgr_nbr
  6.               left outer join c
  7.                on c.dept_nbr = a.dept_nbr
  8.               and c.mgr_nbr = a.mgr_nbr
  9.             where b.dept_nbr is null
  10.                and c.dept_nbr is null
Sep 23 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.