At 10:36 PM 5/12/2004 +0000, you wrote:

>In simple english,
>
>Main Query:
>
>delete any record from the child where the sub query returns at least
>one record.
>
>SubQuery:
>
>Return the value 1 every time you come across a record in the parent
>with a child ID equal to the current child we are checking, and the
>parent f_foo equals 1.
>
>Helen will have to tell you if the following query would provide any
>benefit:
>
>DELETE FROM Child c
>WHERE EXISTS(
> SELECT FIRST 1 1 FROM Parent p
> WHERE p.F_CHILD_ID = c.F_ID AND p.F_FOO = 1)
>
>I have added the 'first 1' directive to say only return the first
>record. It is logically equivalent but just might save firebird some work.

No!!! SELECT FIRST processes the whole table and is not compatible with an
existential query.

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.

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). Your subquery continues processing the
whole table until it has an exhaustive list of all matching records - hence
the slowness. Of course, the EXISTS() subquery will also do this if there
is no matching record, or if the first found record is high in the
cardinality of the table and indexes are not used for the search.

In response to this question:

>Helen, could you explain how this query works?

Answered.

>Is it standard SQL

Yes

>that can be used across different database server?

If you mean, do all RDBMS vendors support all of the
standards? No!!! Low-end DB server implementations are usually pretty
thoroughly cut-down and often don't comply with standards very much at all.

If you mean, can you run the main query on one database on one server and
include a subquery on another database on another server, no, not under any
circumstances. Firebird does not support cross-database queries.

>I don't think understand exactly how this is parsed and how the
>relation between Parent and Child is made here. The JOIN I understand,
>even the Sub-Select I get, but I don't grok this one.

Its logic is: If you find one matching record then the search predicate
returns true immediately and the delete will proceed. Except for your
additional WHERE criterion, it's the way the cascade delete works as well.

Also a comment about your apparent assumption that joins and subqueries are
equivalent: they are not. Some subqueries can return output sets that are
equivalent to left-joined sets but that are capable of being "live" (unlike
the joined set). For selects, this might be the only thing you care about
when extracting the set.

A DELETE statement doesn't return an output set, so there is no rationale
for supposing that you ought to be able to summon the joined set in order
to be able to delete it. In a SP there are techniques whereby you can form
a FOR DELETE cursor set that has criteria using variabl values extracted
from an outer cursor set based on a join --- but, again, SPs are
vendor-specific.

I suspect that you are trying to develop SQL statements in an application
that you want to make generic for any RDBMS. Well, there is theory...and
then there is reality. :-)

./heLen