> Not like that! :-)

That's why I asked :-)

> Tip: unless you have to save white space, it is a REALLY good idea
> to lay out your multi-table statements in a way that makes the
> linkages obvious. Even if you don't care, you *might* retire some
> day and bequeath your code to someone else. :-)

totally agree, but I've never seen an join that is easy to read, I
generally stay with joins in the where clause.

I got it to work, kinda. Here's my SQL:

select Count(*)
from clientinfo C
left join PersonInfo PE
on C.Client_ID = PE.Client_ID
full join PaycheckInfo Pay on PE.Person_ID = Pay.Person_ID
where PE.person_type_id = 0
and Pay.sequence = 1

It returns 2,681 records--and takes 110 seconds to run. If I change it to:

select Count(*)
from clientinfo C
left join PersonInfo PE
on C.Client_ID = PE.Client_ID
where PE.person_type_id = 0

it returns 2,744 records and only takes 31 ms to run.

Questions:

(1) why doesn't it return the same number of records? That is what I
need. (I need a field value from the PaycheckInfo table for every
record in ClientInfo, but the record in PersonInfo and in PaycheckInfo
may not exist)

(2) The plan for the first one is

Plan
PLAN JOIN (PAY NATURAL,JOIN (C NATURAL,PE INDEX (PERSONINFO_CT1)))

Adapted Plan
PLAN JOIN (PAY NATURAL,JOIN (C NATURAL,PE INDEX (PERSONINFO_CT1)))

What causes it to take so long? Paycheck.Client_Id is a FK,
(Person_ID; Sequence) is indexed. I added an index to just Sequence
but that did not speed anything up. What should I look at?

Thanks