CODEWITHSUNDEEP
sqldatabase
Sanya
Sanya

Reputation: 13

Would these two queries provide the same result?

Cities in countries that have a population above 20M (city and country are 2 different table)

Query #1:

SELECT name 
FROM city
WHERE country_id IN (SELECT country_id 
                     FROM country
                     WHERE population > 20000000);

Query #2:

 SELECT name 
 FROM city 
 JOIN country 
 WHERE country.population > 20000000;

Upvotes: 0

Views: 342

Answers (3)

lothakim
lothakim

Reputation: 93

As all I know about SQL, you should slightly change the 2nd query:

SELECT name 
FROM city 
INNER JOIN country ON city.country_id = country.country_id 
WHERE ...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

No, the two are not guaranteed to return the same results. They probably do in your database, but you should use the version that you really want.

The JOIN version can return duplicate rows if country_id is duplicated in the table.

Further, you have not qualified column names, so you could get an error on name in the query with the JOIN.

Upvotes: 2

giorgiga
giorgiga

Reputation: 1778

Yes, but you need to be explicit on the join conditions

SELECT city.name
  FROM city
       JOIN country ON country. country_id = city.country_id
 WHERE country.population > 20000000

Note that, while the two query select the same data, but they are not "the same": one will probably be much faster than the other (depending on your actual data, indexes and what RDBMS you use).

Edit: in case you are figuring SQL out, it might be interesting to consider what the two query would do if city and country are swapped (ie: get the countries where city.population > some number)

Upvotes: 0

Related Questions