Inline Subqueries

An introduction to inline subqueries.

In a previous section we built subqueries using the keyword WITH. Another kind of subquery is an inline subquery. In a standard inline subquery, the subquery is a component of a FROM clause, taking the place of a table name. A caveat of this subquery is that it must must be uncorrelated, i.e., not referencing any fields from the containing query.

An example of an inline subquery in a FROM clause would be:

SELECT manager, regional_office, account,
      COUNT(manager)
 FROM (
   SELECT sales_teams.manager,
              sales_pipeline.sales_agent,
              sales_pipeline.account,
              sales_pipeline.product,
              sales_pipeline.deal_stage,
              sales_teams.regional_office
     FROM sales_teams, sales_pipeline
    WHERE (sales_pipeline.sales_agent = sales_teams.sales_agent)
)  AS `full pipeline`
WHERE deal_stage = "Won"
GROUP BY manager, regional_office, account
ORDER BY COUNT(manager) DESC, manager, account

The results would look like:

manager regional_office account count
Melvin Marxen Central Inity 44
Summer Sewald West Singletechno 42
Melvin Marxen Central Treequote 41
Summer Sewald West Kan-code 41
Melvin Marxen Central Warephase 40
Melvin Marxen Central Codehow 39
Melvin Marxen Central Ron-tech 39
While inline subqueries of this type are supported, it’s always better form to use WITH than a subquery in a FROM clause.