Filtering Rows Using Join and a Sub-query

In this Post I demonstrate how we combine two sets of information and come up with a filtered result set using 'join' and 'sub-queries'.
For this demonstration we use two Excel files which you could download from the following links. One is a list of all Nobel_Laureates and the other is a list of all the countries in the world.

Open SQL Server Management Studio and create a new database with name "Nobel_Laureates".

Create DATABASE Nobel_Laureates

Use Nobel_Laureates

Import the Excel tables to your database. You could refer  how you import Excel data into SQL Server here.
Run the following queries.

SELECT * 

FROM [dbo].[Countries-Continents-csv$]

Now lets filter the countries by continent. We run the following query to see countries only in South America.

SELECT [Country]FROM [dbo].['Countries-Continents-csv$']WHERE [Continent] = 'SOUTH AMERICA'

The partial result set looks like the following.


SELECT * 
FROM [dbo].[List of Nobel Laureates$]

Now we filter the Nobel Laureates by country of birth. Very easy, we could see Nobel Prize Winners who were born in Brazil using the following query.

SELECT * 
FROM [dbo].[List of Nobel Laureates$]
Where [Country of Birth] = 'Brazil'

Now lets try to see Nobel_Laureates from South America, To do that we should know which countries are from South America. You remember We could get that using the following query.

SELECT [Country]
FROM [dbo].['Countries-Continents-csv$']
WHERE [Continent] = 'SOUTH AMERICA'

But what we wanted here is the nobel_Laureates from South America.

SELECT * 
FROM [dbo].[List of Nobel Laureates$]
--Where [Country of Birth] in (countries of south america)

So using a sub-query  the solution for this problem will be

SELECT Winner 
FROM [dbo].[List of Nobel Laureates$]
Where [Country of Birth] IN (SELECT [Country]
                              FROM [dbo].['Countries-Continents-csv$']
                              WHERE [Continent] = 'SOUTH AMERICA'
                             )

But we could also use join to get the same result set. See the following query.

Select Winner
FROM [dbo].[List of Nobel Laureates$] Inner Join [dbo].['Countries-Continents-csv$'] on [Country of Birth] = [country]
where continent = 'south america'