Looking for an equivalent to INTERSECT ALL in sqlite-CodePudding

I have a database which allows duplicates where the key is a string and the values are URLs. Here's the schema: CREATE TABLE Tags (p_tags TEXT, p_urls LONGVARCHAR);

I would like to do a command like this in an efficient manner: "SELECT p_urls FROM Tags WHERE p_tags ='foo' INTERSECT ALL SELECT p_urls FROM Tags WHERE p_tags = 'bar';"

I am aware the INTERSECT ALL is not supported in sqlite, so my best effort right now is to do it by just counting up how many times each URL shows up in each p_tag and taking the minimum of the two. The trouble is that this is very slow - too slow.

Thanks.

CodePudding user response：

You can simulate INTERSECT ALL by adding another column to your resultset with ROW_NUMBER() window function:

WITH cte AS (
  SELECT p_urls, ROW_NUMBER() OVER (PARTITION BY p_urls) rn 
  FROM Tags WHERE p_tags = 'foo' 
  INTERSECT
  SELECT p_urls, ROW_NUMBER() OVER (PARTITION BY p_urls) rn 
  FROM Tags WHERE p_tags = 'bar'
)
SELECT p_urls FROM cte;

Or:

WITH cte AS (
  SELECT p_urls, 
         ROW_NUMBER() OVER (PARTITION BY p_tags, p_urls) rn 
  FROM Tags 
  WHERE p_tags IN ('foo', 'bar')
)
SELECT p_urls 
FROM cte
GROUP BY p_urls, rn
HAVING COUNT(*) > 1;

See a simplified demo.