| sql select query [message #403979] |
Tue, 19 May 2009 23:25  |
mrpranab
Messages: 32
Registered: March 2005
|
Member |
|
|
Hi All,
I have 4 tables named as tab1, tab2, tab3 and tab4. Table tab1 having record count 10 million records and table tab2, tab3 & tab4 having record count 1 million each.
I need to retrieve all data from table tab1 that does not exists in table tab2, tab3 and tab4 i.e. data exists in tab1 but does not exists in tab2, tab3 and tab4 tables.
I don't want to use NOT IN clause. Which other approach will be fine which gives the output faster and the performance of the query is better.
Thank you.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: sql select query [message #404058 is a reply to message #403979] |
Wed, 20 May 2009 04:10  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
If you enter a new topic, you have to enter a title. The purpose of this title is to make your post easier to find amidst the vast number of topics already available.
Now how self-explaining do you think your title is in a SQL forum?
Imagine your bookcase only having titles like "Book" "Lots of words", "Another book", "Sentences", etc. Would you know which is which?
|
|
|
|
| Re: sql select query [message #404060 is a reply to message #403979] |
Wed, 20 May 2009 04:18 |
|
|
You can try out either minus operator or you can use not exists as compared to not in not exists works faster.
select a.field1 from tab1 a
where not exists ( select 1 from tab2 b
where b.field1 = a.field1)
similarly you can nest muliple tables inside each other.
|
|
|
|
| Re: sql select query [message #404115 is a reply to message #403979] |
Wed, 20 May 2009 07:50 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| mrpranab wrote on Wed, 20 May 2009 00:25 |
I need to retrieve all data from table tab1 that does not exists in table tab2, tab3 and tab4 i.e. data exists in tab1 but does not exists in tab2, tab3 and tab4 tables.
|
Do you mean not in ALL the tables, or not in ANY of the tables? Irregardless of what you say, use NOT IN.
|
|
|
|
| Re: sql select query [message #404123 is a reply to message #404115] |
Wed, 20 May 2009 08:14 |
|
|
select a.field1 from tab1 a
where not exists ( select 1 from tab2 b
where b.field1 = a.field1)
and not exists ( select 1 from tab3 c
where c.field1 = a.field1)
and not exists ( select 1 from tab4 d
where d.field1 = a.field1)
Try this out and let me know.This will solve your performance problem as i have practical experience .
|
|
|
|
|
|
|
|
| Re: sql select query [message #404237 is a reply to message #404227] |
Thu, 21 May 2009 01:38 |
|
|
|
Michel dont write such sarcastic comments...i just wanted to help this guy out and i m trying my best to support him and its not a must that you should know environment properly...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
