Thread: left outer join with count
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 doingthis? Gary
Hello Gary,
select sj.*, sr.*,
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 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;
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.stainburn@ringways.co.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
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.