Anti-join on single table

[gxangel]

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!

[abhaysk]

why do u need to use the table twice?

[gxangel]

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!

[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..

[gxangel]

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')

?

[abhaysk]

Yes..

[gxangel]

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!

[gxangel]

WOAH!!!

This is very fast!
Thanks a million!!!

[abhaysk]

Get me the exact requirement (my earlier post has one logical error & i have deleted that one).

[gxangel]

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!