left outer join with count

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

left outer join with count

Gary Stainburn
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



Reply | Threaded
Open this post in threaded view
|

Re: left outer join with count

Gábor SZŰCS
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 <[hidden email]> 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



Reply | Threaded
Open this post in threaded view
|

Re: left outer join with count

Gary Stainburn
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.