Subqueries inside FROM clause

 

Subqueries inside FROM clause (returns a temporary table)

Usage:

• Restructure and transform data into different shape
• A subquery in FROM is an effective way of answering detailed questions that requires filtering or transforming data before including it in your final results.
• Pre-filtering data
• Calculating aggregates of aggregates
• Which 3 teams has the highest average of home goals scored? (AVG, MAX)

Things to remember:

• You can create multiple subqueries in one FROM statement
• Alias them!
• Join them!
• You can join a subquery to a table in FROM
• Include a joining columns in both tables

Subquery as a temporary table in FROM clause. We can have multiple table using a comma in FROM clause.

SELECT DISTINCT monarchs.continent, subquery.max_perc

FROM monarchs,

            (SELECT continent, MAX(women_parli_perc) AS max_perc

                FROM states

                GROUP BY continent) AS subquery

WHERE monarch.continent = subquery.continent

ORDER BY continent;

Question: Determine the number of languages spoken for each country, identified by the country's local name! 

-- Select fields

SELECT local_name, subquery.lang_num

  -- From countries

  FROM countries,

    -- Subquery (alias as subquery)

    (SELECT code, COUNT(*) AS lang_num

     FROM languages

     GROUP BY code) AS subquery

  -- Where codes match

  WHERE countries.code = subquery.code

-- Order by descending number of languages

ORDER BY lang_num DESC;

   

Question:

Generate a subquery using the match table, and then join that subquery to the country table to calculate information about matches with 10 or more goals in total!

SELECT

    -- Select country name and the count match IDs

    c.name AS country_name,

    COUNT(sub.id) AS matches

FROM country AS c

-- Inner join the subquery onto country

-- Select the country id and match id columns

INNER JOIN (SELECT country_id, id 

            FROM match

            -- Filter the subquery by matches with 10+ goals

            WHERE (home_goal + away_goal) >= 10) AS sub

ON c.id = sub.country_id

GROUP BY country_name;

In the previous exercise, you found that England, Netherlands, Germany and Spain were the only countries that had matches in the database where 10 or more goals were scored overall. Let's find out some more details about those matches -- when they were played, during which seasons, and how many of the goals were home vs. away goals.

You'll notice that in this exercise, the table alias is excluded for every column selected in the main query. This is because the main query is extracting data from the subquery, which is treated as a single table.

SELECT

    -- Select country, date, home, and away goals from the subquery

    country,

    date,

    home_goal,

    away_goal

FROM 

    -- Select country name, date, home_goal, away_goal, and total goals in the subquery

    (SELECT c.name AS country, 

            m.date, 

            m.home_goal, 

            m.away_goal,

           (m.home_goal + m.away_goal) AS total_goals

    FROM match AS m

    LEFT JOIN country AS c

    ON m.country_id = c.id) AS subq

-- Filter by total goals scored in the main query

WHERE total_goals >= 10;