CODEWITHSUNDEEP
sqlpostgresql
Nikhil Bharadwaj
Nikhil Bharadwaj

Reputation: 11

converting subquery to join in postgresql

    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

Upvotes: 0

Views: 294

Answers (1)

LordF
LordF

Reputation: 439

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.

Upvotes: 1

Related Questions