-
Anti-join on single table
Hi,
I need to optimize a SQL query which does an anti-join on one table.
select A from table_t
where A not in
(select distinct A from table_t
where B in ('ABC','DEF') and C ='LS')
I read about using NOT EXISTS in place of NOT IN but i can't seem to implement it into my query as the examples shown uses anti-join on 2 tables.
Is there a way to optimize an anti-join on one table? Will appreciate it if you can show me a link or so.
Thanks a lot!
-
why do u need to use the table twice?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
I get you point!
You see, i was trying very hard to avoid using NOT IN/NOT EXISTS but it turns out that the situation is worse now that i scan the table twice.
This is the original query:
select A from table_t
where B not in ('ABC','DEF') and C ='LS'
I guess i just have to live with it.
Thanks abhaysk!
-
if this the original query.. then the query in ur first post not only uses table twice but also logically not in sync with this query..
live with what you have..
Last edited by abhaysk; 09-28-2007 at 09:15 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hi abhaysk,
Do you mean that
select A from table_t
where B not in ('ABC','DEF') and C ='LS'
is different from
select A from table_t
where A not in
(select distinct A from table_t
where B in ('ABC','DEF') and C ='LS') ?
-
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
I get what you mean ...
Created a table to see the results.
Supposed i have a table table_t:
ID OCDATE TC
1 9/26/2006 CB
1 9/26/2006 UT
2 9/26/2006 CS
2 9/26/2006 UT
3 9/26/2006 CS
4 9/26/2006 CB
5 9/26/2006 CS
4 9/26/2006 SS
5 9/26/2006 SS
6 9/26/2006 SS
7 9/26/2006 SS
Select id from table_t where
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc not in ('CB', 'CS', 'UT')
ID
6
7
5
4
select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
ID
1
1
2
2
3
4
5
4
5
6
7
select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and id not in (
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc not in ('CB', 'CS', 'UT')
)
ID
1
1
2
2
3
Is there a way to avoid 2 table scans on table_t for this query?
select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and id not in (
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc not in ('CB', 'CS', 'UT')
)
Thanks abhaysk for pointing out my mistake!
-
WOAH!!!
This is very fast!
Thanks a million!!!
-
Get me the exact requirement (my earlier post has one logical error & i have deleted that one).
Last edited by abhaysk; 10-01-2007 at 06:29 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hi abhaysk,
just found out that your previous post doesn't give the correct results.
Anyway, here's my requirements.
Supposed i have a table table_t:
ID OCDATE TC
1 9/26/2006 CB
1 9/26/2006 UT
2 9/26/2006 CS
2 9/26/2006 UT
3 9/26/2006 CS
4 9/26/2006 CB
5 9/26/2006 CS
4 9/26/2006 SS
5 9/26/2006 SS
6 9/26/2006 SS
7 9/26/2006 SS
Output should be:
ID
1
1
2
2
3
My original query:
select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and id not in (
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc not in ('CB', 'CS', 'UT')
)
Thanks alot for your time and effort!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|