CODEWITHSUNDEEP
sqlpostgresqlsubquery
AdR
AdR

Reputation: 305

Understanding use of where in subqueries

List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

enter image description here

From https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial, question 3

This is what I tried

select name, continent
from world
where continent = (select continent 
   from world where name in ('Argentina',' Australia'))
order by name

Thought it was about equivalence, so tried this

select name, continent
from world
where continent in (select continent 
   from world where name in ('Argentina',' Australia'))
order by name

and this seems to be the solution

select name, continent
from world
where continent = (select continent 
   from world where name ='Argentina')
or continent = (select continent 
   from world where name ='Australia')
order by name

I'm trying to understand the logical flaw in my solution.

Upvotes: -1

Views: 3008

Answers (3)

Deeksha Shandilya
Deeksha Shandilya

Reputation: 15

Your second solution is correct, but because of the single space before ' Australia' in query (select continent from world where name IN ('Argentina',**' Australia'**) ) is giving you wrong output. You are not getting Continent name Oceania.

I hope this has given you clarity on the query.

select name,continent from world 
where continent IN 
(select continent from world where name IN ('Argentina','Australia')) 
order by name

Upvotes: 0

Syed Abid Hasan
Syed Abid Hasan

Reputation: 1

This worked for me

select name,continent from world
     where continent IN ( select continent
     from world where name IN ('Argentina','Australia')) 
     ORDER BY name

Upvotes: 0

Fərid Qənbərli
Fərid Qənbərli

Reputation: 118

Becasue subquery in your first result will return multiple rows, and you can't use equal sign there so you have to check 'in' clause.

Upvotes: 1

Related Questions