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, SQL Server displays an error message.
However, this can be unreliable and should aften be avoided because 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 suppose each publisher to be located in only one city, and you want to find the names of authors who live in the city where Algodata Infosystems is located, you can write a statement with a subquery introduced with the simple comparison operator = (equal sign):
SELECT au_lname, au_fname
FROM authors
WHERE city =
(SELECT city
FROM publishers
WHERE pub_name = 'Algodata Infosystems')
au_lname au_fname
-------- --------
Carson Cheryl
Bennet Abraham
(2 row(s) affected)
If, however the preceding subquery returned multiple rows, then an error message would result, stating that it was an illegal subquery. Instead of =, an IN formulation could be used (= ANY also works).
Subqueries introduced with unmodified comparison operators often include aggregate functions, since these return a single value. For example, this statement finds the names of all books priced higher than the current minimum price:
SELECT distinct title
FROM titles
WHERE price >
(SELECT min(price)
FROM titles)
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 that 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 in the trad_cook category:
SELECT DISTINCT title
FROM titles
WHERE price >
(SELECT min(price)
FROM titles
GROUP BY type
HAVING type = 'trad_cook')