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)