Special Uses of Inline Subqueries

An introduction to special uses of in-line 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
The above example for EXISTS contains a correlated subquery as it uses a field (sales_pipeline.account) from outside the query. Correlated subqueries are only allowed in EXISTS, IN, and comparison subqueries.

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:

close_date sales_agent account close_value
2017-06-27 Markita Hansen Goodsilron 29,617
2017-04-05 Rosalina Dieter Y-corporation 25,288
2017-06-26 James Ascencio Y-corporation 7,356
2017-04-20 Elease Gluck Lexiqvolax 23,746
2017-06-28 Elease Gluck Plexzap 24,949
2017-06-18 Elease Gluck Labdrill 27,385
2017-08-14 Markita Hansen Finjob 24,745
2017-08-28 Rosalina Dieter Rantouch 24,899
2017-08-29 Rosalina Dieter Kan-code 25,791
2017-09-26 Markita Hansen Cheers 29,166
2017-06-30 Elease Gluck Cheers 25,464
2017-03-09 Elease Gluck Zoomit 25,897
2017-11-10 Rosalina Dieter Xx-holding 26,186
2017-06-07 Rosalina Dieter Groovestreet 30,288
2017-10-29 Elease Gluck Cheers 27,971
2017-12-04 Elease Gluck Xx-holding 29,220
2017-11-25 James Ascencio Lexiqvolax 6,932

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
)
close_date sales_agent account product close_value
2017-06-07 Rosalina Dieter Groovestreet GTK 500 30288