Slide 15.12: The EXISTS predicate

◀
Previous

Slide 15.11: The quantified comparison predicate
Slide 15.13: A weakness of SQL: Too many equivalent forms
Home Print version

▶
Next

The EXISTS Predicate

The “EXISTS (subquery)” predicate tests whether the set of rows retreived in a subquery is non-empty.

The EXISTS predicate is TRUE if and only if the subquery returns a non-empty set.

[ NOT ] EXISTS (subquery)

Note: Oracle does NOT support Boolean data type; i.e., the Boolean values, TRUE and FALSE, are not supported, but an expression such as EXISTS can return a Boolean value.

Question XV (EXISTS Predicate)
Retrieve all customer names where the customer places an order through agent a05.

SELECT DISTINCT c.cname FROM customer c
  WHERE EXISTS ( SELECT * FROM order3 o
    WHERE c.cid = o.cid AND o.aid = 'a05' );

NOT EXISTS can be used to implement the MINUS operator. If R and S are two compatible table (with Head(R) = Head(S) = A₁ ... A_n), then the SQL for difference R–S:

SELECT A1 ... An FROM R WHERE NOT EXISTS ( SELECT * FROM S
  WHERE S.A1 = R.A1 AND ... AND S.An = R.An );

Question XVI (NOT EXISTS Predicate)
Find cid values of customers who do not place any order through agent a03.

order3[cid] – (order3 WHERE aid = 'a03')[cid];

SELECT DISTINCT cid FROM order3 o WHERE NOT EXISTS (
  SELECT * FROM order3 WHERE cid = o.cid AND aid = 'a03' );

order3
orderno	month	cid	aid	pid	qty	dollar
1011	jan	c001	a01	p01	1000	450.00
1012	jan	c001	a01	p01	1000	450.00
1019	feb	c001	a02	p02	400	180.00
1017	feb	c001	a06	p03	600	540.00
1018	feb	c001	a03	p04	600	540.00
1023	mar	c001	a04	p05	500	450.00
1022	mar	c001	a05	p06	400	720.00
1025	apr	c001	a05	p07	800	720.00
1013	jan	c002	a03	p03	1000	880.00
1026	may	c002	a05	p03	800	704.00
1015	jan	c003	a03	p05	1200	1104.00
1014	jan	c003	a03	p05	1200	1104.00
1021	feb	c004	a06	p01	1000	460.00
1016	jan	c006	a01	p01	1000	500.00
1020	feb	c006	a03	p07	600	600.00
1024	mar	c006	a06	p01	800	400.00

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
c004

(Question 16)

⇒

cname
TipTop
Basics

(Question 15)

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