www.tartoos.com Comparison Operators Modified by ANY, SOME, or ALL

العودة إلى مدرسة الكمبيوتر قسم البرمجة الصفحة الثانية

Comparison Operators Modified by ANY, SOME, or ALL

Comparison operators that introduce a subquery can be modified by the keywords ALL or ANY. SOME is an SQL-92 standard equivalent for ANY.

Subqueries introduced with a modified comparison operator return a list of zero or more values and can include a GROUP BY or HAVING clause. These subqueries can be restated with EXISTS.

Using the > comparison operator as an example, >ALL means greater than every value; in other words, greater than the maximum value. For example, >ALL (1, 2, 3) means greater than 3. >ANY means greater than at least one value, that is, greater than the minimum. So >ANY (1, 2, 3) means greater than 1.

For a row in a subquery with >ALL to satisfy the condition specified in the outer query, the value in the column introducing the subquery must be greater than each value in the list of values returned by the subquery.

Similarly, >ANY means that for a row to satisfy the condition specified in the outer query, the value in the column that introduces the subquery must be greater than at least one of the values in the list of values returned by the subquery.

-----------------------------------------------

Note This example can be run many different ways, as long as the inner query returns only one value.

----------------------------------------------

USE pubs

-- Option 1 using MAX in the inner query

SELECT title

FROM titles

HAVING MAX(advance) > ALL

WHERE advance > ALL

(

SELECT MAX(advance)

FROM publishers INNER JOIN titles ON

titles.pub_id = publishers.pub_id

WHERE pub_name = 'Algodata Infosystems'

)

-- Option 2 using GROUP BY and HAVING and no ALL

USE pubs

SELECT title

FROM titles

GROUP BY title

HAVING MAX(advance) >

(

SELECT MAX(advance)

FROM publishers INNER JOIN titles ON

titles.pub_id = publishers.pub_id

WHERE pub_name = 'Algodata Infosystems'

)

The following query provides an example of a subquery introduced with a comparison operator modified by ANY. It finds the titles that received an advance larger than the minimum advance amount paid by Algodata Infosystems.

USE pubs

SELECT title

FROM titles

WHERE advance > ANY

(SELECT advance

FROM publishers INNER JOIN titles

ON titles.pub_id = publishers.pub_id

AND pub_name = 'Algodata Infosystems')

Here is the result set:

title

---------------------------------------------------------------

You Can Combat Computer Stress!

The Gourmet Microwave

But Is It User Friendly?

Secrets of Silicon Valley

Computer Phobic and Non-Phobic Individuals: Behavior Variations

Life Without Fear

Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean

Sushi, Anyone?

(8 row(s) affected)

For each title, the inner query finds a list of advance amounts paid by Algodata. The outer query looks at all values in the list and determines whether the title currently being considered has commanded an advance larger than any of those amounts. In other words, it finds titles with advances as large or larger than the lowest value paid by Algodata.

If the subquery does not return any values, the entire query fails to return any values.

The =ANY operator is equivalent to IN. For example, to find authors who live in the same city as a publisher, you can use either IN or =ANY.

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE city IN

(SELECT city

FROM publishers)

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE city = ANY

(SELECT city

FROM publishers)

Here is the result set for either query:

au_lname au_fname

-------- ---------

Carson Cheryl

Bennet Abraham

(2 row(s) affected)

The < >ANY operator, however, differs from NOT IN: < >ANY means not = a, or not = b, or not = c. NOT IN means not = a, and not = b, and not = c. <>ALL means the same as NOT IN.

For example, this query finds the authors who live in a city in which no publisher is located.

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE city <> ANY

(SELECT city

FROM publishers)

Here is the result set:

au_lname au_fname

---------------------------------------- --------------------

White Johnson

Green Marjorie

Carson Cheryl

O'Leary Michael

Straight Dean

Smith Meander

Bennet Abraham

Della Buena Ann

Gringlesby Burt

Locksley Charlene

Greene Morningstar

Blotchet-Halls Reginald

Yokomoto Akiko

del Covello Innes

DeFrance Michel

Stringer Dirk

MacFeather Stearns

Karsen Livia

Panteley Sylvia

Hunter Sheryl

McBadden Heather

Ringer Anne

Ringer Albert

(23 row(s) affected)

The results include all 23 authors because every author lives in a city in which one or more of the publishers is not located. The inner query finds all the cities in which publishers are located, and then, for each city, the outer query finds the authors who don’t live there.

However, when you use NOT IN in this query, the results include all the authors except Cheryl Carson and Abraham Bennet, who live in Berkeley, where Algodata Infosystems is located.

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE city NOT IN

(SELECT city

FROM publishers)

Here is the result set:

au_lname au_fname

---------------------------------------- --------------------

White Johnson

Green Marjorie

O'Leary Michael