As always Helen, thanks for your considered response.

>
> Robert, both your IN() predicate and the EXISTS() predicate are
> existential queries - that is, they work by testing for existence.
> The difference is that EXISTS() tests only until it finds the first
> matching row - hence, logically, the match *exists*, the search
> condition is met and the subquery exits.
>

I didn't know that Camus was into databases. You say that EXISTS tests
only until it finds the first matching row. Are you saying that the
QUERY in EXITS is executed for each record? I noticed EXISTS or IN are
not using my index on ID in the CHILD table. I suppose I'm just
confused why I can't query the PARENT table, return a bunch of
CHILD_IDs and then use the ID index in the Child table to delete all
those that match in a jiffy.

> Firebird will internally resolve an IN() subquery to this far less
> costly predicate, if the arguments provided to the IN() clause can
> supply the keys (not the case in your example).

Why is this not the case in my example? I am selecting F_CHILD_ID in
Parent where F_CHILD_ID = Child.F_ID (which is the first column of the
primary key in Child). Should I be making F_ID it's own index?

>
> Also a comment about your apparent assumption that joins and
> subqueries are equivalent: they are not.
>

Sorry, I didn't mean to imply that they are equivalent, just that in
the following two examples that their results are the same except that
(A) takes 12-20 mins while (B) takes a few milliseconds:

A: SELECT *
FROM Child
WHERE Child.F_ID IN (
SELECT Parent.F_CHILD_ID
FROM Parent
WHERE Parent.FOO = 1)

B: SELECT *
FROM Child
JOIN Parent ON Child.F_ID = Parent.F_CHILD_ID
WHERE Parent.FOO = 1

It's seems kind of sad that I cannot create a DELETE query as fast as
(B) and must settle for the slowness of query (A). And like I said,
profiling the difference between IN() and EXISTS() in this case shows
little difference. The query returns around 680 records out of
9,000,000 records in the table.

Ok, so instead of the EXIST() or IN() (which seem like my only option
for a single-statement delete), do you think it would be faster to
have an ONDELETE trigger on Parent that just deletes the appropriate
children, thus allowing me to just do this:

DELETE FROM Parent WHERE Parent.FOO = 1

Would it also be faster than the EXISTS() or IN() to just do this from
Java?

rset = [SELECT F_CHILD_ID FROM Parent WHERE FOO = 1]
while ( rset.next() )
{
[DELETE FROM Child WHERE F_ID = rset.getLong(1)]
}

I suppose the trigger would be the fastest, but would the client-side
loop also be faster than the IN()/EXISTS() variant?

>
> I suspect that you are trying to develop SQL statements in an
> application that you want to make generic for any RDBMS.
>

You are very perceptive as usual.

R.