CODEWITHSUNDEEP
sql
Polukanti Gouthamkrishna
Polukanti Gouthamkrishna

Reputation: 1

What is the difference between the following queries

I have seen this question from sql zoo select from world tutorial(13 th one)

SELECT 
    name, continent,
    CASE 
       WHEN continent = 'Oceania' THEN 'Australasia'
       WHEN continent = 'Eurasia' OR name = 'Turkey' THEN 'Europe/Asia'
       WHEN continent = 'Caribbean' AND name LIKE 'b%' THEN 'North America'
       WHEN continent = 'Caribbean' AND name NOT LIKE 'b%' THEN 'South America'
       ELSE continent 
    END
FROM world
ORDER BY name;

SELECT
    name, continent AS 'Original Continent',
    CASE WHEN continent = 'Oceania' THEN 'Australasia' 
         WHEN continent = 'Eurasia' OR name = 'Turkey' THEN 'Europe/Asia'
         WHEN continent IN (SELECT continent FROM world 
                            WHERE continent = 'Caribbean' 
                              AND name LIKE 'B%') THEN 'North America' 
         WHEN continent IN (SELECT continent FROM world 
                            WHERE continent = 'Caribbean' 
                              AND name NOT LIKE 'B%') THEN 'South America'  
         ELSE continent 
    END as 'New Continent'     
FROM  
    world 
ORDER BY
    name;

the first one shows the right results and the second didn't what is the difference? Can any one please answer.

Upvotes: 0

Views: 59

Answers (1)

GarethD
GarethD

Reputation: 69749

In this line:

WHEN continent IN (SELECT continent 
                    FROM world 
                    WHERE continent = 'Caribbean' 
                    AND name LIKE 'B%') THEN 'North America'

You are assigning all countries in the Carribean with North America, since your IN part:

(SELECT continent FROM world WHERE continent = 'Caribbean' AND name LIKE 'B%')

will return 

continent
-------------
Caribbean
Caribbean
Caribbean

You essentially have 

 WHEN continent IN ('Caribbean') THEN 'North America'

Which is not correct, since it won't assign any of the countries to the continent of South America. Using a subquery version you would need to use name rather than continent:

WHEN name IN (SELECT name FROM world WHERE continent = 'Caribbean' AND name LIKE 'B%')
    THEN 'North America' 
WHEN name IN (SELECT name FROM world WHERE continent = 'Caribbean' AND name NOT LIKE 'B%') 
    THEN 'South America'

Upvotes: 1

Related Questions