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.

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.

Answer

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.

Advertisement

Answer