CODEWITHSUNDEEP
mysqlsql
TDo
TDo

Reputation: 744

MySQL-sub-queries inside IN()

This is my first post and I am a beginner in SQL so really hope you guys can help me with this question.

When I want to write sub-queries inside IN(), I can only write one sub-query. If I put in one more there will be error. I can't figure out why. Hope someone can help

This works fine:

SELECT name, continent
FROM world
WHERE continent IN (SELECT continent FROM world WHERE name ='belize')

However whenever I put in one more sub-query, there is an error.

SELECT name, continent
FROM world
WHERE continent IN (SELECT continent FROM world WHERE name ='belize',
                    SELECT continent FROM world WHERE name ='belgium')

Really appreciate if someone can help me with this. Thanks a lot in advance.

Upvotes: 0

Views: 58

Answers (4)

Paul Maxwell
Paul Maxwell

Reputation: 35583

(  SELECT continent FROM world WHERE name ='belize'
 , SELECT continent FROM world WHERE name ='belgium' )

Appears you have assumed that a subquery is needed for each value in the list. i.e. that from the 2 subqueries you would achieve this:

('Europe', 'Europe')

But this is NOT the way SQL treats a subquery IN ( here )

As soon as you use a subquery, it treats that subquery as producing a COLUMN of potential values. So don't try to build the "in list" one value at a time, just do it through one column.

Consider this simple query:

SELECT DISTINCT continent FROM world

It will produce a COLUMN of results

Africa
Antarctica
Asia
Australia
Europe
North America
South America

And if we used that simple query IN ( here ) like so:

SELECT country, continent FROM world
WHERE continent IN ( SELECT DISTINCT continent FROM world )

It would work fine, and list all countries from the continents. It's not a very sensible query really, but the point is that the single subquery can return many rows, and each row's value is evaluated.

Ironically you could use both your subqueries by doing a UNION or UNION ALL like this:

SELECT name, continent
FROM world
WHERE continent IN (SELECT continent FROM world WHERE name ='belize'
                    UNION ALL
                    SELECT continent FROM world WHERE name ='belgium')

This is wasteful of resources but it is valid syntax because it is building a COLUMN of results which is allowed.

Upvotes: 0

Peter Bowers
Peter Bowers

Reputation: 3093

Here's an answer which doesn't use a sub-query which (in my experience) is almost always more efficient:

SELECT DISTINCT name, continent 
    FROM world
    JOIN world world2 ON world.continent = world2.continent
    WHERE world2.name IN('belize' , 'belgium')

Now that I look at this I realize that the necessity of the DISTINCT clause (to prevent duplicating rows if you specified 2 countries that were in the same continent - not a problem in your specific example, but I assume could be part of future edge conditions) probably makes this less efficient. Better to stick with the sub-query.

Upvotes: 0

Dyrandz Famador
Dyrandz Famador

Reputation: 4525

just use OR to condition if any of the two exist. 

SELECT name, continent 
FROM world 
WHERE continent IN (
   SELECT continent 
   FROM world 
   WHERE name ='belize' OR name ='belgium'
)

Upvotes: 1

shauryachats
shauryachats

Reputation: 10385

Since, you are SELECTing from the same table, you can combine the two statements using OR condition.

SELECT name, continent FROM world 
WHERE continent IN 
(
SELECT continent FROM world 
WHERE name = 'belize' 
OR name = 'belgium'
);

Upvotes: 2

Related Questions