title: Replacing Inner Join with a SubQuery in SQL
body: <p>Pardon if this question is too simple as I am in the process of learning how to write a subquery.
  I came across this guide online, which shows an example of how a SQL statement with INNER JOIN can be replaced by one with a Subquery in the SELECT clause.</p>
  
  <p>There are 2 tables - Countries (code, name, continent, region, surface_area) and Cities (name, country_code, city_proper_pop, metroarea_pop).</p>
  
  <p>SQL statement with INNER JOIN</p>
  
  <pre><code>SELECT countries.name AS country, COUNT(*) AS cities_num
        FROM cities
          INNER JOIN countries
          ON countries.code = cities.country_code
      GROUP BY country
      ORDER BY cities_num DESC, country
      LIMIT 9;
  </code></pre>
  
  <p>This returns the table:
  <a href="https://i.stack.imgur.com/cS2N6.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cS2N6.png" alt="enter image description here"></a></p>
  
  <p>Next, this statement is replaced by a SQL statement with a Subquery in the SELECT clause</p>
  
  <pre><code>    SELECT countries.name AS country,
        (SELECT COUNT(*)
         FROM cities
         WHERE countries.code = cities.country_code) AS cities_num
      FROM countries
      ORDER BY cities_num DESC, country
      LIMIT 9;
  </code></pre>
  
  <p>which shows the <strong>same resulting table</strong>.</p>
  
  <p>My question is:
  I am unsure why the nested SQL query can contain this condition <strong>countries.code = cities.country_code</strong> in the WHERE clause without performing any Join. Because the nested query only draws result from <strong>cities table</strong>, it does not specify the <strong>countries table</strong>.</p>
  
  <p>Hope someone can explain to me. Thank you in advance.</p>