## Using SOME(ANY) and ALL keywords with comparison predicates |
||||||||||||||

SOME and ANY are synonyms, i.e. any of them may be used. The subquery results is a single value column. If any value V returned by the subquery evaluates the operation "<expression value> <comparison operator> V" to TRUE, the ANY predicate is also equal to TRUE.
is similar to that with ANY, except that all values returned by the subquery must meet the predicate "<expression> <comparison operator> V ". Example 5.8.1 Find the PC makers whose models are not presently sold (i.e. they are not available in the PC table):
It turns out that maker E has not supplied their models from sale:
Let us consider that example in details. The predicate
Example 5.8.2 Find the models and prices for laptops with priced above any PC:
Here are the formal rules for evaluating the predicate with ANY|SOME and ALL parameters: - If ALL or SOME parameter are given and all the comparison results of expression value with each value returned by the subquery are equal to TRUE, truth value is TRUE.
- If the result set of the subquery does not have any rows with the ALL parameter specified, the result is TRUE. However, if the SOME parameter is specified, the result is equal to FALSE.
- If the ALL parameter has been specified and comparison of the expression value with at least one value obtained from the subquery gives FALSE, the truth value is equal to FALSE.
- If the SOME parameter is specified and comparison of the expression value with at least one value obtained from the subquery gives TRUE, the truth value is equal to TRUE.
- If the SOME parameter is specified and each comparison of the expression value with the values obtained from the subquery gives FALSE, the truth value is also equal to FALSE.
- Otherwise, the result evaluates to UNKNOWN.
Suggested exercises: 17, 24, 51, 67, 68, 74 , 79, 127 |

aggregate functions
Airport
ALL
AND
AS keyword
ASCII
AVG
Battles
Bezhaev
Bismarck
C.J.Date
calculated columns
Cartesian product
CASE
cast
CHAR
CHARINDEX
Chebykin
check constraint
classes
COALESCE
common table expressions
comparison predicates
Computer firm
CONVERT
correlated subqueries
COUNT
CROSS APPLY
CTE
data type conversion
data types
database schema
date/time functions
DATEADD
DATEDIFF
DATENAME
DATEPART
DATETIME
DDL
DEFAULT
DEFAULT VALUES
DELETE
DISTINCT
DML
duplicates
EXCEPT
exercise (-2)
exercise 19
exercise 23
exercise 32
More tags

exercise 37
exercise 39
exercise 46
exercise 54
exercise 55
exercise 56
exercise 57
exercise 7
exercise 70
exercise 8
exercises
EXISTS
FLOAT
FROM
FULL JOIN
GROUP BY
grouping
Guadalcanal
HAVING
head ships
IDENTITY
IN
income
inner join
INSERT
INTERSECT
IS NOT NULL
IS NULL
ISNULL
join operations
laptop
launched year
LEFT
LEFT OUTER JOIN
LEN
LIKE
LTRIM
MAX
MIN
mistakes
MySQL
NOT
NOT IN
NULL
NULLIF
number sequences
number-sequence generation
numbering
ON DELETE CASCADE
OR
Oracle
ORDER BY
outcome
Outcomes
outer joins
OVER
paging
Painting
PARTITION BY
Pass_in_trip
PATINDEX
PC
PIVOT
PostgreSQL
predicates
primary key
printer
product
Ranking functions
recursive CTE
renaming columns
REPLACE
RIGHT
RIGHT JOIN
ROUND
rounding
ROW_NUMBER
ships
sorting
SQL Server
SQL Server 2012
SQL-92
sql-ex.ru
string functions
subquery
SUBSTRING
SUM
tables join
tips and solutions
Torus
Transact-SQL
Trip
TRUNCATE TABLE
type conversion
UNION
UNION ALL
UNKNOWN
UPDATE
varchar
WHERE
window functions
WITH
XML
XPath
XQuery
упражнение 23

The book was updated

*month ago*