CODEWITHSUNDEEP
sqlsql-server
Swathi Sarangarajan
Swathi Sarangarajan

Reputation: 1

How to convert SQL subquery to Joins

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]);

Upvotes: 0

Views: 100

Answers (2)

user1529235
user1529235

Reputation:

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

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.

Upvotes: 1

Related Questions