("MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000
Database Design and Implementation Exam 70-229, Second Edition") I am
looking at the section on the ANY/ALL keyword.
<QUOTE>
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')
This statement finds the titles that received an advance larger than
the minimum advance amount paid by Algodata Infosystems (which, in this
case, is $5,000). The WHERE clause in the outer SELECT statement
contains a subquery that uses a join to retrieve advance amounts for
Algodata Infosystems. The minimum advance
amount is then used to determine which titles to retrieve from the
Titles table.
</QUOTE
I don't understand why this references the "minimum advance". If you
run the subquery on its own, it returns the following values:
5000.0000
5000.0000
5000.0000
7000.0000
8000.0000
NULL
>From my limited understanding, the "ANY" keyword applies to at least
one value, but which one? How is this determined?
Any help gratefully received.
Edward
--
The reading group's reading group:
http://www.bookgroup.org.ukThe value to the left of the comparison operator (Advance) is compared
to each of the values returned by the subquery. If it matches at least
one of those values then the result is True. Think of it as a series of
comparions linked by OR. For example your query could be expanded to
the following, which is logically equivalent:
SELECT Title
FROM Titles
WHERE
(advance > 5000
OR advance > 5000
OR advance > 5000
OR advance > 7000
OR advance > 8000
OR advance > NULL)
--
David Portas
SQL Server MVP
--|||David Portas wrote:
> The value to the left of the comparison operator (Advance) is
compared
> to each of the values returned by the subquery. If it matches at
least
> one of those values then the result is True. Think of it as a series
of
> comparions linked by OR. For example your query could be expanded to
> the following, which is logically equivalent:
> SELECT Title
> FROM Titles
> WHERE
> (advance > 5000
> OR advance > 5000
> OR advance > 5000
> OR advance > 7000
> OR advance > 8000
> OR advance > NULL)
Thanks, David, that's extremely helpful. Am I right in thinking that
if I substitute the "ALL" keyword for the "ANY" keyword in the original
query, the expansion as above would be:
SELECT Title
FROM Titles
WHERE
(advance > 5000
AND advance > 5000
AND advance > 5000
AND advance > 7000
AND advance > 8000
AND advance > NULL)
If so, I'm back with the programme.
Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk|||That's correct. I should also say that there is a subtle catch that if
the subquery is empty then ALL always returns True whereas the ANY
returns False. Try:
SELECT Title
FROM Titles
WHERE Advance > ANY
(
SELECT Advance
FROM Publishers INNER JOIN Titles
ON Titles.Pub_id = Publishers.Pub_id
AND 1=0)
SELECT Title
FROM Titles
WHERE Advance > ALL
(
SELECT Advance
FROM Publishers INNER JOIN Titles
ON Titles.Pub_id = Publishers.Pub_id
AND 1=0)
--
David Portas
SQL Server MVP
--|||David Portas wrote:
> That's correct. I should also say that there is a subtle catch that
if
> the subquery is empty then ALL always returns True whereas the ANY
> returns False. Try:
> SELECT Title
> FROM Titles
> WHERE Advance > ANY
> (
> SELECT Advance
> FROM Publishers INNER JOIN Titles
> ON Titles.Pub_id = Publishers.Pub_id
> AND 1=0)
> SELECT Title
> FROM Titles
> WHERE Advance > ALL
> (
> SELECT Advance
> FROM Publishers INNER JOIN Titles
> ON Titles.Pub_id = Publishers.Pub_id
> AND 1=0)
Interesting, but I've been using SQL without either ANY or ALL for
about ten years, so I guess I'll carry on without. However, the book
I'm studying has alerted me to CUBE and ROLLUP which I can see some
serious uses for.
Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk
