Subqueries with Comparison Operators

Subqueries with Comparison Operators

Subqueries can be introduced with one of the comparison operators (=, < >, >, > =, <, ! >, ! <, or < =).

A subquery introduced with an unmodified comparison operator (a comparison operator not followed by ANY or ALL) must return a single value rather than a list of values, like subqueries introduced with IN. If such a subquery returns more than one value, Microsoft® SQL Server™ displays an error message.

To use a subquery introduced with an unmodified comparison operator, you must be familiar enough with your data and with the nature of the problem to know that the subquery will return exactly one value.

For example, if you assume each publisher is located in only one city, and you want to find the names of authors who live in the city in which Algodata Infosystems is located, you can write a statement with a subquery introduced with the simple = comparison operator.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city =
   (SELECT city
   FROM publishers
   WHERE pub_name = 'Algodata Infosystems')

Here is the result set:

au_lname      au_fname
--------      --------
Carson      Cheryl
Bennet      Abraham

(2 row(s) affected)

If, however, Algodata Infosystems was located in multiple cities, then an error message would result. Instead of the = comparison operator, an IN formulation could be used (= ANY also works).

Subqueries introduced with unmodified comparison operators often include aggregate functions, because these return a single value. For example, this statement finds the names of all books priced higher than the current minimum price.

USE pubs
SELECT DISTINCT title
FROM titles
WHERE price >
   (SELECT MIN(price)
   FROM titles)

Here is the result set:

title
----------------------------------------------------------
But Is It User Friendly?
Computer Phobic and Non-Phobic Individuals: Behavior Variations
Cooking with Computers: Surreptitious Balance Sheets
Emotional Security: A New Algorithm
Fifty Years in Buckingham Palace Kitchens
Is Anger the Enemy?
Life Without Fear
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Prolonged Data Deprivation: Four Case Studies
Secrets of Silicon Valley
Silicon Valley Gastronomic Treats
Straight Talk About Computers
Sushi, Anyone?
The Busy Executive's Database Guide

(14 row(s) affected)

Because subqueries introduced with unmodified comparison operators must return a single value, they cannot include GROUP BY or HAVING clauses unless you know the GROUP BY or HAVING clause itself returns a single value. For example, this query finds the books priced higher than the lowest priced book that has a type 'trad_cook'.

USE pubs
SELECT DISTINCT title
FROM titles
WHERE price >
   (SELECT MIN(price)
   FROM titles
   GROUP BY type
   HAVING type = 'trad_cook')

Here is the result set:

title                                                                   
------------------------------------------------------------------------
But Is It User Friendly?                                                
Computer Phobic AND Non-Phobic Individuals: Behavior Variations         
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean         
Prolonged Data Deprivation: Four Case Studies                           
Secrets of Silicon Valley                                               
Silicon Valley Gastronomic Treats                                       
Straight Talk About Computers                                           
Sushi, Anyone?                                                          
The Busy Executive's Database Guide                                     

(9 row(s) affected)