CODEWITHSUNDEEP
sqlpostgresqlsubqueryinner-joinouter-join
Trident D'Gao
Trident D'Gao

Reputation: 19762

Subquery or union of joins in postgres?

I have so-called links that can have tags assigned to them, so I store it in 3 tables:

Now I need to get basic tag counts: how many times a tag was used (including 0 times). I have two queries:

select t.id, t.name, count(*)
from tag as t inner join tag_in_link as tl
    on tl.tag_id = t.id
group by t.id, t.name
union
select t.id, t.name, 0
from tag as t left outer join tag_in_link as tl
    on tl.tag_id = t.id where tl.tag_id is null

union of joins explained

and

select t.id, t.name,
       (select count(*) from tag_in_link as tl
              where tl.tag_id = t.id
       ) as count from tag as t

correlated subquery

they both give the same (up to the order of records) results and work almost as fast

Problem is that I don't have much data to test it, but I need to pick one way or another today. All I know is that, there will be:

So my question:

Upvotes: 1

Views: 825

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 248030

The first query will be better for large data sets, because it does not force a nested loop.

But why don't you use the optimal query:

SELECT t.id, t.name, count(*)
FROM tag AS t LEFT JOIN tag_in_link AS tl
    ON tl.tag_id = t.id
GROUP BY t.id, t.name;

Upvotes: 1

Parfait
Parfait

Reputation: 107767

Consider combining UNION with a conditional aggregation, still avoiding the correlated subquery run for every row.

select t.id, t.name, 
       sum(case when tl.tag_id is null then 0 else 1 end) as tag_count
from tag as t 
left join tag_in_link as tl
    on tl.tag_id = t.id
group by t.id, t.name

Upvotes: 0

Related Questions