Home » SQL & PL/SQL » SQL & PL/SQL » Sub Query with IN
Sub Query with IN [message #222486] Mon, 05 March 2007 02:37 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I'm writing the following query -

select cif_key from fem_term_deposits where cif_key not in
(seect cif_key from fem_parties)

fem_term_deposits & fem_parties are a huge tables , is there a better way of writing query??

Thanks
Re: Sub Query with IN [message #222489 is a reply to message #222486] Mon, 05 March 2007 02:55 Go to previous messageGo to next message
friendarora
Messages: 24
Registered: February 2007
Junior Member

can u try this



select a.cif_key
from fem_term_deposits a ,fem_parties b
where a.cif_key <> b.cif_key
Re: Sub Query with IN [message #222492 is a reply to message #222489] Mon, 05 March 2007 03:08 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
SQL> desc test_2
Name Null? Type
----------------------------------------- -------- ----------------------------
NO NUMBER

SQL> desc test_1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NO NUMBER

SQL> select * from test_1;

NO
----------
1
2
3

SQL> select * from test_2;

NO
----------
1
3

SQL> select a.no from test_1 a, test_2 b where a.no <> b.no ;

NO
----------
2
3
1
2

SQL>


I want the output 2.

Thanks
Re: Sub Query with IN [message #222554 is a reply to message #222492] Mon, 05 March 2007 07:05 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
You might take a look at these links and see if NOT EXISTS or minus work for you. Make sure to look at how NOT IN and NOT EXISTS will handle nulls.


NOT IN vs. NOT EXISTS
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/b0ab65e819e4ff46/6932ac6a7bd2aac3%236932ac6a7bd2aac3

NOT EXISTS vs. MINUS
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/aeeae66da39a6b86/fa3b4de337729c8%23fa3b4de337729c8


Check here for the difference between NOT IN and NOT EXISTS when null values are present
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:442029737684
Re: Sub Query with IN [message #222555 is a reply to message #222492] Mon, 05 March 2007 07:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that when you say
I want the output 2.
you mean that you want the output to consist of a single row containing the value 2, you can try:
SELECT no FROM test_1
MINUS
SELECT a FROM test_2;


Assuming there aren't too many distinct values in test_1.no, this should be fairly efficient.
Previous Topic: Query needed
Next Topic: time in microseconds
Goto Forum:
  


Current Time: Sat Dec 10 22:43:24 CST 2016

Total time taken to generate the page: 0.05501 seconds