473,324 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Can JOIN be used as alternative to NOT EXIST

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
2 10634
rahulj
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
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

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

Similar topics

46
by: Leo Breebaart | last post by:
I've tried Googling for this, but practically all discussions on str.join() focus on the yuck-ugly-shouldn't-it-be-a-list-method? issue, which is not my problem/question at all. What I can't...
4
by: thilbert | last post by:
All, I have a perplexing problem that I hope someone can help me with. I have the following table struct: Permission ----------------- PermissionId Permission
3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
8
by: xixi | last post by:
when i create a join view like this create view JV104FZ.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05, PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select JV104FZ.APPTINM.APAM32,...
3
by: Martin | last post by:
Hello everybody, I have the following question. As a join clause on Oracle we use " table1.field1 = table2.field1 (+) " On SQL Server we use " table1.field1 *= table2.field1 " Does DB2...
149
by: Christopher Benson-Manica | last post by:
(Followups set to comp.std.c. Apologies if the crosspost is unwelcome.) strchr() is to strrchr() as strstr() is to strrstr(), but strrstr() isn't part of the standard. Why not? --...
4
by: Prasanth Raveendran | last post by:
Through an ASP.Net page I want to browse a shared folder. So i used directory.exist(\\machinename\sharename) but it returns false even if the directory exist. please help me -- Prasanth...
1
by: sword | last post by:
This my first starting a new topic in English. I want to ask if I want to judge whether a file exists how I should do. I program with C++. Thank you.
4
by: Jane T | last post by:
I appreciate how difficult it is to resolve a problem without all the information but maybe someone has come across a similar problem. I have an 'extract' table which has 1853 rows when I ask for...
0
realin
by: realin | last post by:
hi guys, i have two tables as thread_post post_id thread_id thread_cat thread_post_id
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.