Slide 15.11: The quantified comparison predicate

Slide 15.10: The SQL scoping rule
Slide 15.12: The EXISTS predicate
Home Print version

The Quantified Comparison Predicate

A quantified predicate compares the simple value of an expression with the result of a subquery.

expr θ { SOME | ANY | All } (subquery),
  where θ is some operator in the set { <, <=, =, <>, >, >= }

Question XIII (Quantified Predicates)
Find aid values of agents with a minimum percent commission.

agent
aid	aname	city	percent
a01	Smith	New York	6
a02	Jones	Newark	6
a03	Brown	Tokyo	7
a04	Gray	New York	6
a05	Otasi	Duluth	5
a06	Smith	Dallas	5

⇒

aid
a05
a06

SELECT aid FROM agent
  WHERE percent <= ALL ( SELECT percent from agent );

The equivalent predicates, “expr θ SOME (subquery)”, and “expr θ ANY (subquery)”, are TRUE if and only if, for at least one element s returned by the subquery, it is true that “expr θ s”.

Question XIV (Equivalent Predicate)
Find all customers who have the same discount as that of any of the customers in Dallas or Boston.

customer
cid	cname	city	discnt
c001	TipTop	Duluth	10.00
c002	Basics	Dallas	12.00
c003	Allied	Dallas	8.00
c004	ACME	Duluth	8.00
c006	ACME	Kyoto	0.00

⇒

cid	cname
c002	Basics
c004	ACME
c003	Allied

SELECT cid, cname FROM customer
  WHERE discnt = SOME ( SELECT discnt FROM customer
    WHERE city = 'Dallas' OR city = 'Boston' );

Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.

Result:

The Database includes:

Tablename	Record
Customers	91
Categories	8
Employees	10
OrderDetails	518
Orders	196
Products	77
Shippers	3
Suppliers	29

W3C WebSQL

WebSQL Browser Support