CODEWITHSUNDEEP
sqlpostgresql
fs2050
fs2050

Reputation: 87

How to improve a postgresql query?

can someone show me how to improve the below query and make it one statement instead of two statements? Thanks

CREATE LOCAL TEMP TABLE tmpdetail2
WITH (OIDS) 
ON COMMIT DROP as
  select d2.detailid, d2.objid, d2.p
  from _detail d2                         
  where d2.detailid in (19, 106);  

  select distinct d.detailid, d.p, d.pval, d.objid
  from _detail d 
  left join tmpdetail2 d2
    on d.objid = d2.objid
  where d2.objid is null 
    and d.p not in(select p from tmpdetail2)  
  order by p asc, d.detailid asc;

Upvotes: 1

Views: 56

Answers (1)

user330315
user330315

Reputation:

Use a common table expression:

with tmpdetail2 as (
  select d2.detailid, d2.objid, d2.p
  from _detail d2                         
  where d2.detailid in (19, 106)  
)
select distinct d.detailid, d.p, d.pval, d.objid
from _detail d 
left join tmpdetail2 d2
  on d.objid = d2.objid
where d2.objid is null 
  and d.p not in(select p from tmpdetail2)  
order by p asc, d.detailid asc;

Upvotes: 2

Related Questions