How to convert IN and NOT IN subselect to JOIN?

Question

I have:

with cm as (select n from tbl1 where c = 41)    
select nn from tbl2  
where 
    n in (select n from cm) and
    nn not in (select n from cm);

This conversion does not seem to work:

 select tbl2.nn from tbl2 join tbl1
 on tbl1.n = tbl2.n and tbl1.n != tbl2.nn
 where tbl1.c = 41;

Answer 1

Either way, you have to join to the same table twice.
With joins instead of IN:

WITH cm AS (SELECT n FROM tbl1 WHERE c = 41)
SELECT t.nn
FROM   tbl2    t
JOIN   cm      c1 ON c1.n = t.n
LEFT   JOIN cm c2 ON c2.n = t.nn
WHERE  c2.n IS NULL;

The same without CTE:

SELECT t.nn
FROM   tbl2      t
JOIN   tbl1      t1 USING (n)  -- equivalent to "ON t1.n = t.n" here
LEFT   JOIN tbl1 t2 ON t2.n = t.nn
                   AND t2.c = 41  -- must go here!
WHERE  t1.c = 41
AND    t2.n IS NULL;

Requires that cm.n is UNIQUE / tbl1.n is UNIQUE for tbl1.c = 41.

See:

• Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
• Two SQL LEFT JOINS produce incorrect result

While cm.n is never NULL, your original works, too:

WITH cm AS (SELECT n FROM tbl1 WHERE c = 41)
SELECT t.nn
FROM   tbl2 t
WHERE  n      IN (TABLE cm)
AND    nn NOT IN (TABLE cm);

Simplified with short syntax TABLE. See:

• Is there a shortcut for SELECT * FROM?

But I'd rather use NOT EXISTS instead of NOT IN. Typically faster and does not fail surprisingly with NULL values.

WITH cm AS (SELECT n FROM tbl1 WHERE c = 41)
SELECT t.nn
FROM   tbl2 t
WHERE  EXISTS     (SELECT FROM cm WHERE n = t.n)
AND    NOT EXISTS (SELECT FROM cm WHERE n = t.nn)

EXISTS never duplicates rows (like IN). And it works as expected when NULL values are involved (like JOIN). So it's typically my first choice.

See:

• Select rows which are not present in other table