Act fast, special offers end soon! Up to $294 is savings when you get Premium today.
Get offer codes

Combining Joins with Subqueries

Hi,

Here’s the query that I wrote and it took me a really long time to write it. However, I still need some help in understanding the questions that I have.

Chapter: Joining Data in SQL
Mission: “Combining Joins with Subqueries”
Link: Mission Link

Correct Query

SELECT cities.name AS 'capital_city', facts.name AS 'country',
cities.population AS 'population'
    FROM facts
    INNER JOIN 
            (SELECT * 
                 FROM cities
                 WHERE cities.capital = 1
                 AND cities.population > 10000000) AS cities
                 ON cities.facts_id = facts.id
 ORDER BY cities.population DESC
 LIMIT 10;

Incorrect Query where I was stuck

SELECT cities.name AS 'capital_city', facts.name AS 'country',
cities.population AS 'population'
    FROM facts
    INNER JOIN 
            (SELECT * 
                 FROM cities
                 WHERE cities.capital = 1
                 AND cities.population > 10000000) [missing alias]
                 ON cities.facts_id = facts.id
 ORDER BY cities.population DESC
 LIMIT 10;

My questions:

  1. The subquery does not use an alias. So, Why do we need an Alias compulsorily after the closing parentheses of the subquery?

My second question is based on the following observation. Here’s the query answering the same question without using a subquery.

SELECT cities.name AS capital_city, facts.name AS 'country', cities.population AS 'population'
                        FROM facts
                        INNER JOIN cities ON cities.facts_id = facts.id
                        WHERE cities.capital = 1 AND cities.population > 10000000
                        ORDER BY cities.population DESC
                        LIMIT 10;
  1. How to know when we need subquery to join the data with joins?

Rahul Rewani

hi @rewani.r

Have you been able to work this out on your own? If not let me know if this helps.

  1. Joins need two tables to connect, each table with it’s own unique name. Even if we join a table to itself, we would still need unique identifiers, for example:
    • Customer table joins Purchase table => C joins P ON C.column = P.column
    • Employee table joins Employee table => E as e joins E as s (where e and s represent identifiers for employee and supervisor) ON e.column = s.column

Considering the above, if we are joining with a subquery which can be interpreted as a dynamic table we create, shouldn’t that also have a unique identifier?
Thus the alias is required. The sub-query will fetch the result, but SQL will still want to know, what two or more tables it needs to join to complete the query and fetch the results for the parent query.

  1. There is no per say thumb rule to use a sub-query alone or with a join. It will depend on what final outcome you want from your most external query and how complex the whole code is. Sub-queries essentially help us form simpler, manageable, smaller & stand-alone query parts. More details here.
    SQL Subqueries - w3resource
1 Like

Hi @Rucha,
Thanks much for your detailed response. The example in terms of tables makes it very clear.

So, if I understand it right, sometimes, some questions can be answered without using a subquery?

Takeaway: Subquery makes the code easier to manage and understand and when to use the subquery will depend on the following two factors:

  • What question are we looking to answer in the most external query?
    and
  • How complex the whole code is? (Subqueries can simplify and make the code more manageable, right?)

Yes, please. Can I please ask you for more resources?

Rahul Rewani

hi @rewani.r

You can check out this article. I haven’t gone through the related resources provided in this, but overall it should help you to understand sub-query and join mechanisms.

Subquery vs. JOIN | LearnSQL.com