converting subquery to join in postgresql

Question

    select p.postid postid,p.posttypeid posttypeid,
   (select count(parentid) as no_of_answers from post
    where parentid=p.postid), p.title title,
    p.body body ,
   p.creationdate creationdate,
   p.modifieddate modifieddate,
   p.modifieduserid modifieduserid,
   p.score score,p.views no_of_views,
    u.userid userid 
   from post 
    as p 
    left outer join user_quest as u on p.owneruserid = u.userid
    where p.posttypeid = 1
    Order by p.creationdate desc
    LIMIT 10 OFFSET 0;

I need to convert his subquery to join, please help

Why do you "need" to convert that?

user330315
– user330315

2021-02-04 11:25:59 +00:00
Commented Feb 4, 2021 at 11:25 — user330315
– user330315, Commented Feb 4, 2021 at 11:25

LordF · Accepted Answer · 2021-02-04 11:20:22Z

1

You could use CTE:

WITH counts AS (
    SELECT 
        count(parentid) AS no_of_answers,
        parentid
    FROM post
    GROUP BY parentid
)
SELECT  
    p.postid,
    p.posttypeid,
    p.title,
    COALESCE(c.no_of_answers, 0) AS no_of_answers,
    p.body,
    p.creationdate,
    p.modifieddate,
    p.modifieduserid,
    p.score,
    p.views AS no_of_views,
    u.userid 
FROM post AS p 
    LEFT JOIN counts c ON (c.parentid = p.postid)
    LEFT JOIN user_quest AS u ON (p.owneruserid = u.userid) 
WHERE p.posttypeid = 1
    ORDER BY p.creationdate DESC
    LIMIT 10 OFFSET 0;

or put yours subquery to JOIN:

SELECT  
    p.postid,
    p.posttypeid,
    p.title,
    COALESCE(c.no_of_answers, 0) AS no_of_answers,
    p.body,
    p.creationdate,
    p.modifieddate,
    p.modifieduserid,
    p.score,
    p.views AS no_of_views,
    u.userid 
FROM post AS p 
    LEFT JOIN 
    (
        SELECT 
            count(parentid) AS no_of_answers,
            parentid
        FROM post
        GROUP BY parentid
    ) c ON (c.parentid = p.postid)
    LEFT JOIN user_quest AS u ON (p.owneruserid = u.userid) 
WHERE p.posttypeid = 1
    ORDER BY p.creationdate DESC
    LIMIT 10 OFFSET 0;

But I would prefer CTE. The performance of CTEs and subqueries should, in theory, be the same since both provide the same information to the query optimizer. One difference is that a CTE used more than once could be easily identified and calculated once. And it's look pretties because is easier to read, at least i thing so.

edited Feb 4, 2021 at 11:20

answered Feb 4, 2021 at 9:17

LordF

5367 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Frank Heikens Over a year ago

Why both LEFT JOIN and LEFT OUTER JOIN? Can be confusing for beginners

LordF Over a year ago

I used LEFT JOIN because it's shorter and LEFT OUTER JOIN was in question and I only coppied this, but yes I agree it could be confusing. I changed that.

Collectives™ on Stack Overflow

converting subquery to join in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related