Re: Finding Orphans

Paul Keister wrote:
>
> One thing you could try is to group the child tables before you do the
> nested select, i.e.
>
> SELECT count(*)
> FROM address
> WHERE addr_key NOT IN
> (
> SELECT addr_key FROM client
> UNION
> SELECT addr_key FROM employee
> UNION
> SELECT addr_key FROM groups
> UNION
> SELECT addr_key FROM patient
> UNION
> SELECT addr_key FROM provider
> )
>
> I'm not sure this will help because I'm not sure why your original version
> isn't working right. This query should be more efficient, unless the query
> optimizer has already figured things out: you only want to scan all the
> child tables once, then form a single unique set of IDs to compare against.
> That's what this should do.
>
> The first question I always ask myself when I start bloodying my forehead
> is: is this really wrong? Perhaps you don't actually have any Orphans.
>
> "Lucky Leavell [RIS]" <ris_at_iglou.com> wrote in message
> news:Pine.GSO.4.31.0101081607350.21269-100000_at_shell1...
> > I have a situation whereby I am trying to determine the number of orphans
> > in an addresses table by checking for those address keys NOT IN the five
> > tables which use it, to wit:
> >
> >
> > SELECT count(*)
> > FROM address
> > WHERE addr_key NOT IN (SELECT addr_key
> > FROM client)
> > AND addr_key NOT IN (SELECT addr_key
> > FROM employee)
> > AND addr_key NOT IN (SELECT addr_key
> > FROM groups)
> > AND addr_key NOT IN (SELECT addr_key
> > FROM patient)
> > AND addr_key NOT IN (SELECT addr_key
> > FROM provider)

Shouldn't the original version provide better performance as long as there are foreign keys defined?

Regards,
Heinz Received on Tue Jan 09 2001 - 16:06:47 CET