Re: left outer join with count

Hello Gary,

Depends on way too many things, but assuming
- You are concerned about cardinality of wd Vs SJ
- SJ.sj_id is unique
- there are necessary indexes in place
...
Then I'd recommend subselect in SELECT clause, like:

select sj.*, sr.*,
(select count(sj_id) from work_documents wd where
wd.sj_id=SJ.sj_id) doc_count
from
service_jobs sj
left outer join service_receptions sr on sr.sr_id = sj.sj_sr_id;

(Note, haven't verified syntax)

Maybe if you could send explain plans and index info...

Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> ezt írta (időpont: 2020.
máj. 29., P 18:09):

> I have the following select:
>
> select sj.*, sr.*, wd.doc_count from
> service_jobs sj
> left outer join service_receptions sr on sr.sr_id = sj.sj_sr_id
> left outer join (select sj_id, count(sj_id) as doc_count from
> work_documents wd group by sj_id) wd on wd.wd_sj_id = sj.sj_id;
>
> While the select works, I can't help thinking that the last join is
> expensive. Is there a cleaner (quicker) method of doing this?
>
> Gary
>
>
>
>