loading..
Русский    English
13:28

Using SOME(ANY) and ALL keywords with comparison predicates

  1. < expression > < comparison operator > SOME|ANY (< subquery >)

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.

  1. < expression > < comparsion operator > ALL (< subquery >)

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):

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product
  3. WHERE type = 'pc' AND
  4. NOT model = ANY (SELECT model
  5. FROM PC
  6. )

It turns out that maker E has not supplied their models from sale:

Maker
E

Let us consider that example in details. The predicate

  1. model = ANY (SELECT model
  2. FROM PC
  3. );
returns TRUE if the model specified by the model column in the main query will be in the PC-table model list (returned by the subquery). Because of the predicate using the NOT negation, TRUE will be obtained unless the model is in the list. That predicate is checked for each row in the main query that return all PC models (type = 'PC' predicate) in the Product table. The result set consists of single column - maker's name. The DISTINCT keyword is used to eliminate any maker duplicates that may occur when a maker produces more than one model absent from the PC table.

Example 5.8.2

Find the models and prices for laptops with priced above any PC:

Console
Execute
  1. SELECT DISTINCT model, price
  2. FROM Laptop
  3. WHERE price > ALL (SELECT price
  4. FROM PC
  5. )

model Price
1298 1050
1750 1200
1752 1150

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


Bookmark and Share
Развернуть всё
Свернуть всё

Content:

Tags
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
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100