"NOT IN"/"NOT EXISTS" for pairwise column comparison not working in Redshift-CodePudding

I would like to rewrite the below statement in redshift. Redshift does not support IN/NOT IN for multiple column comparison. I get the following error when I try the same query with NOT EXISTS as well:

Invalid operation: This type of correlated subquery pattern is not supported due to internal error;

select count(distinct item) from tbl1  
where (item,store) not in (
select distint item, store form tbl1 
where *store changed* -- some filters
)

CodePudding user response：

Tim's idea seems cool, but you can also use the following code:

select     count(distinct t1.item)
from       tbl1 t1
left join
(
        select distinct item, store
        form   tbl1 
        where  *store changed* -- some filters
) t2
on t2.item = t1.item
and t2.store = t1.store

where t2.item is null
and   t2.store is null

CodePudding user response：

Your version of Redshift does not support this tuple syntax. You may rephrase as follows:

SELECT COUNT(DISTINCT item) AS cnt
FROM tbl1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM tbl1 t2
    WHERE t2.item = t1.item AND
          t2.store = t1.store AND
          (other filters here...)
);