How to convert SQL subquery to Joins

Question

How do I convert a SQL subquery to Joins:

SELECT [COUNTRY] FROM [CountryMappingDB].[dbo].[DNB_Country_mapping_poc.ss] 
WHERE [COUNTRY] NOT IN 
(SELECT [EntityAttributeValue]  FROM [CountryMappingDB].[dbo].[DNB_MappingData.ss]
UNION ALL
SELECT [SalesCountryName] FROM [CountryMappingDB].[dbo].[DNB_Country_mapping_poc_dimSalesCountry.ss]);

Possible duplicate of [How to convert a SQL subquery to a join](https://stackoverflow.com/questions/600943/how-to-convert-a-sql-subquery-to-a-join) — demo, Jul 17 '17 at 13:44

score 1 · Answer 1 · answered Jul 17 '17 at 13:46

I think the best way to write the query is:

SELECT [COUNTRY]
FROM [CountryMappingDB].[dbo].[DNB_Country_mapping_poc.ss]  ss
WHERE NOT EXISTS (SELECT 1
                  FROM [CountryMappingDB].[dbo].[DNB_MappingData.ss] ss2
                  WHERE ss2.County = ss.Country
                 ) AND
      NOT EXISTS (SELECT 1
                  FROM [CountryMappingDB].[dbo].[DNB_Country_mapping_poc_dimSalesCountry.ss] ss3
                  WHERE ss3.SalesCountryName = ss.Country
                 );

Although you can write the query using JOINs, I think this is more easily understood and it will work as expected when the comparison tables have NULL values.

score 0 · Answer 2 · answered Jul 17 '17 at 13:48

Try this;

SELECT [COUNTRY] FROM [CountryMappingDB].[dbo].[DNB_Country_mapping_poc.ss] 
left join
    (SELECT MyData=[EntityAttributeValue]  FROM [CountryMappingDB].[dbo].[DNB_MappingData.ss]
    UNION ALL
    SELECT MyData=[SalesCountryName] FROM [CountryMappingDB].[dbo].[DNB_Country_mapping_poc_dimSalesCountry.ss]
    ) countries on [COUNTRY]=countries.MyData
WHERE countries.MyData is null

How to convert SQL subquery to Joins

2 Answers2