sql - How to rewrite a subquery with a join

Question

Heyho, I need to rewrite the following code without useing a subquery:

SELECT country, region, name
FROM wcities
WHERE( country, region ) NOT IN ( SELECT country, code FROM regions )
AND country = 'IT'
AND region is NOT NULL;

I tried it with:

select c.country, c.region, c.name
from wcities c
join regions r on c.country <> r.country AND c.region <> r.code
where c.country = 'IT' 
AND c.region is NOT NULL;

I can see why that does not work, but I can't see what I need to do it.

Thanks in advance :)

Answer 1

As a left join this would be:

SELECT c.country, c.region, c.name
FROM wcities c LEFT JOIN
     regions r
     ON r.country = c.country and r.region = c.region
WHERE r.country IS NULL AND
      c.country = 'IT' AND
      c.region is NOT NULL;

That said, I would recommend NOT EXISTS for this purpose:

SELECT c.country, c.region, c.name
FROM wcities c         
WHERE NOT EXISTS (SELECT 1
                  FROM regions r
                  WHERE r.country = c.country and r.region = c.region
                 ) AND
      c.country = 'IT' AND
      c.region is NOT NULL;