Re: Advanced SQL question: NOT EXISTS

stowe_at_mcs.net wrote:
} > vincen5158_at_aol.com (Vincen5158) writes:
} > >>
} > NOT EXISTS isn't a good choice -- for anything. It's very inefficient.
} > (If you think about it, you'll
} > realize that it requires that the ENTIRE table be checked for the value.)
} > That being said, I'm not
} > sure that NOT EXISTS is a good choice for what you're attempting to
} > accomplish, either
} > >>
} > For the RULE based optimization I've found just the opposite, EXISTS is
} > much quicker than IN. Its the IN clause that essentially has to create a
} > complete result set (after all IN compares against a list) each time to
} > satisify the query. EXISTS determines whether the condition is true and
} > uses indexes to satisfy it. I'd be interested in your experiences on this.
} >>>>
 

} EXISTS is not the same as NOT EXISTS. If you consider it for a moment, EXISTS allows the
} query to stop running as soon as a match is found, but NOT EXISTS forces the entire table to be
} checked in order to rule out a match.

Won't NOT EXISTS use indexes though?

ie:	select COLUMN_A 
	from TABLE_B
	where not exists
		(select COLUMN_C
		from TABLE_D
		where COLUMN_C = COLUMN_A);

I am not saying that this is "good", but it is a lot better than using NOT IN.

Tom

--

Thomas M. Buccelli (tmb_at_idm.com)

#include <std_disclaimer.h>