Re: left outer join with count

On Saturday 30 May 2020 05:43:06 Gábor SZŰCS wrote:
> 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...

Thank you for this. I ended up doing it as two separate queries. Seems to be quicker.