Simplify and/or optimize sql query with INTERSECT or HAVING

Question

I have the following data:

 tag_id | taggable_id 
--------+-------------
      1 |           1
      2 |           1
      3 |           1
      4 |           1
      1 |           2
      2 |           2
      5 |           2
      6 |           2
      7 |           3
      8 |           3
      9 |           3
     10 |           3

And I want to bring all the taggable_ids which are in a group of tag_ids AND in another group AND another... (max 4 groups).
For example:

if I search all the taggable_ids with tag_id 1 or 7 AND tag_id 4 should return just 1
if I search all the taggable_ids with tag_id 1 AND tag_id 6 AND tag_id 2 or 8 should return just 2
if I search all the taggable_ids with tag_id 8 AND tag_id 5 should not return ids

In a gross way(for the second example), the query is the following:

SELECT taggable_id FROM taggings WHERE tag_id in (1)
INTERSECT
SELECT taggable_id FROM taggings WHERE tag_id in (6)
INTERSECT
SELECT taggable_id FROM taggings WHERE tag_id in (2,8)

I think simplifying it, it looks like:

SELECT taggable_id FROM taggings WHERE tag_id in (1,2,6,8)
GROUP BY taggable_id HAVING COUNT(*)=3

but I'm wondering if can be done in a simpler way. Any thoughts?

Your query does not agree with your description. And your 2nd one is *not* equivalent as the first. It would be prudent to present a consistent example. — Erwin Brandstetter, Dec 05 '14 at 22:23

score 3 · Accepted Answer · edited May 23 '17 at 11:50

This can be cast as a case of relational division. We have assembled an arsenal of query techniques under this related question:

How to filter SQL results in a has-many-through relation

Depending on data distribution and other factors, this may be fastest:

SELECT DISTINCT taggable_id
FROM   taggings t1
JOIN   taggings t2 USING (taggable_id)
JOIN   taggings t3 USING (taggable_id)
WHERE  t1.tag_id = 1
AND    t2.tag_id = 6
AND    t3.tag_id IN (2, 8);

Assuming unique (tag_id, taggable_id), DISTINCT is actually not needed for the example. But it might be necessary with other (list) predicates.

SQL Fiddle (building on @Clodoaldo's, thanks).

score 2 · Answer 2 · answered Dec 05 '14 at 19:59

SQL Fiddle

Your second query fails if the tuple (8, 2) is inserted. Here is one solution although I don't know if simpler then the intersect one:

select taggable_id
from taggings
where tag_id in (1,2,6,8)
group by taggable_id
having
    array_agg(tag_id) @> array[1,2]
    and
    array_agg(tag_id) && array[6,8]

score 1 · Answer 3 · answered Dec 05 '14 at 19:55

Your second query is not equivalent to the first. Change the having clause to:

HAVING SUM(CASE WHEN tag_id = 1 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN tag_id = 6 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN tag_id IN (2, 8) THEN 1 ELSE 0 END) > 0;

As for performance, test the queries to see which performs better. The advantage of the having approach is you can add more and more complex conditions without having a significant impact on performance.

Simplify and/or optimize sql query with INTERSECT or HAVING

3 Answers3