Special Uses of Inline Subqueries

Inline subqueries also have specialized uses in several other contexts:

• In EXISTS or NOT EXISTS expressions.
• On the right side of IN or NOT IN expressions.
• On the right side of comparison expressions with the modifiers ANY, ALL or SOME.
• As scalar subqueries anywhere an expression is allowed. In that case the subquery is subject to several restrictions. It must return exactly one row and one column. It may not contain GROUP BY or HAVING clauses, nor may it use UNION, INTERSECTION or MINUS. Finally, the subquery must must be uncorrelated, i.e., not referencing any fields from the containing query.

Following are some examples for each of these subquery types.

EXISTS or NOT EXISTS

If we wanted to know which closed and won deals in the pipeline were with companies that had more than 10,000 employees, we could run this query:

SELECT created_date,
       sales_agent,
       account,
       product,
       close_value
  FROM sales_pipeline
 WHERE EXISTS (
            SELECT *
              FROM accounts
             WHERE employees > 10000
                   AND sales_pipeline.account = accounts.account)
AND sales_pipeline.deal_stage = "Won"

|created_date|sales_agent|account|product|close_value| |- |2017-02-23|Kary Hendrixson|Kan-code|GTX Plus Pro|5,782| |2017-02-23|Zane Levy|Kan-code|GTX Plus Basic|929| |2017-02-25|Cassey Cress|Initech|GTX Basic|542| |2017-02-27|Kary Hendrixson|Kan-code|GTXPro|3,893| |2017-02-27|Reed Clapper|Kan-code|GTX Plus Basic|1,106| |2017-02-27|Violet Mclelland|Kan-code|MG Advanced|3,749| |2017-03-01|Boris Faz|Kan-code|GTX Plus Basic|1,053| |2017-03-01|Reed Clapper|Kan-code|MG Advanced|3,558| |2017-03-01|Vicki Laflamme|Xx-holding|GTX Basic|523| |2016-12-01|Cassey Cress|Initech|MG Advanced|3,463| |2017-03-02|Kary Hendrixson|Xx-holding|GTX Basic|599| |2017-03-02|Kary Hendrixson|Kan-code|GTXPro|4,506| |2017-03-04|Reed Clapper|Kan-code|GTX Basic|521|

IN or NOT IN

This query returns a list of all the closed and won deals in the pipeline for products which cost over $1,000:

SELECT created_date,
       sales_agent,
       account,
       product,
       close_value
  FROM sales_pipeline
 WHERE product IN (
        SELECT *
          FROM products
         WHERE sales_price > 1000)
AND sales_pipeline.deal_stage = "Won"

|created_date|sales_agent|account|product|close_value| |- |2016-10-20|Moses Frase|Cancity|GTX Plus Basic|1,054| |2017-02-22|Daniell Hammack|Zathunicon|GTX Plus Pro|4,848| |2017-02-22|Hayden Neloms|Fasehatice|MG Advanced|3,763| |2017-02-22|Kami Bicknell|Sonron|GTX Plus Pro|5,491| |2017-02-22|Markita Hansen|Y-corporation|GTX Plus Pro|5,503| |2017-02-22|Niesha Huffines|Genco Pura Olive Oil Company|GTX Plus Basic|1,051| |2017-02-22|Niesha Huffines|Conecom|GTX Plus Pro|5,604| |2017-02-22|Reed Clapper|Xx-zobam|MG Advanced|3,273| |2017-02-22|Zane Levy|Scotfind|GTX Plus Basic|1,220| |2017-02-22|Zane Levy|Singletechno|GTX Plus Pro|5,497| |2016-11-26|Niesha Huffines|Globex Corporation|GTX Plus Basic|1,080| |2017-02-23|Anna Snelling|Conecom|GTX Plus Basic|1,056| |2017-02-23|Cecily Lampkin|Funholding|MG Advanced|3,668| |2017-02-23|Daniell Hammack|Silis|MG Advanced|3,509|

ANY, ALL or SOME

For a nicely complicated query we could want to know which sales people in the western regional office had deals with a close value higher than all of the sales agents in the eastern regional office. Because the regional office information isn’t stored in the pipeline we would need to use joins in both our main query and our subquery:

SELECT pipeline.close_date,
       pipeline.sales_agent,
       pipeline.account,
       pipeline.close_value
  FROM sales_pipeline AS pipeline, sales_teams AS teams
 WHERE pipeline.sales_agent = teams.sales_agent
       AND teams.regional_office = "West"
       AND pipeline.close_value >= ALL (
            SELECT pipeline.close_value
              FROM sales_pipeline AS pipeline, sales_teams AS teams
             WHERE pipeline.sales_agent = teams.sales_agent
                   AND teams.regional_office = "East")
       AND pipeline.deal_stage = "Won"

The results would look like this:

Scalar Subqueries

We would use a scalar subquery if we wanted to know the largest deal in the pipeline:

SELECT close_date,
       sales_agent,
       account,
       product,
       close_value
  FROM sales_pipeline
 WHERE close_value = (
    SELECT MAX(close_value)
      FROM sales_pipeline
)