any and all,
exists, not exists.
The general forms of in and not in predicates:
expr in (sub-select)
expr in (value {, value})
expr not in (sub-select)
expr not in (value {, value})
(1) Get cid values of customers who place orders with
agents in "Dallas" or "Duluth".
First we get aids of agents in "Dallas" or
"Duluth":
SQL> select aid
from agents
where city = 'Duluth' or city = 'Dallas';
AID
---
a05
a06
Then we use the previous select in the search
condition of the following select statement:
SQL> select distinct cid
from orders
where aid in (select aid
from agents
where city = 'Duluth' or
city = 'Dallas');
CID
----
c001
c002
c004
c006
(2) Get all information about agents in "Dallas" or
"Duluth".
SQL> select *
from agents
where city in ('Duluth', 'Dallas');
AID ANAME CITY PERCENT
--- ------------- -------------------- ----------
a05 Otasi Duluth 5
a06 Smith Dallas 5
(3) Multiple nesting: Get names and discounts of
customers who place orders through agents in
"Dallas" or "Duluth".
SQL> select cname, discnt
from customers
where cid in
(select cid
from orders
where aid in
(select aid
from agents
where city in
('Duluth', 'Dallas')));
CNAME DISCNT ------------- ---------- Tiptop 10 Basics 12 ACME 8 ACME 0
(4) Correlated sub-select: subselect that uses data
from an outer select.
Get names of customers who order product "p05".
SQL> select cname
from customers
where 'p05' in (select pid
from orders
where cid = customers.cid);
CNAME
-------------
Tiptop
Allied
(5) Scope of variables: outer select cannot refer to
variables inside inner-selects.
SQL> select cname
from customers
where orders.aid = 'a03' and
'p07' in (select pid
from orders
where cid = customers.cid);
** ILLEGAL SQL SYNTAX ** as outer select refers to
orders.aid
(6) Get ordno values for orders placed by customers
in "Duluth" through agents in "New York".
SQL> select ordno
from orders
where cid in (select cid
from customers
where city = 'Duluth') and
aid in (select aid
from agents
where city = 'New York');
ORDNO
----------
1011
1012
1023
The general forms of quantified comparison predicates:
expr <any (sub-select)
expr <=any (sub-select)
expr =any (sub-select)
expr <>any (sub-select)
expr >any (sub-select)
expr >=any (sub-select)
expr <all (sub-select)
expr <=all (sub-select)
expr =all (sub-select)
expr <>all (sub-select)
expr >all (sub-select)
expr >=all (sub-select)
(7) Get aid values of agents with the smallest
percent commission.
SQL> select aid
from agents
where percent <=all (select percent
from agents);
AID
---
a05
a06
(8) Get names of customers who have the same discount
as that of any (one) of the customers in "Dallas"
or "Boston".
SQL> select cname
from customers
where discnt =any (select discnt
from customers
where city = 'Dallas' or
city = 'Boston');
CNAME
-------------
Allied
ACME
Basics
(9) Get cid values of customers with smaller
discounts than every customer from "Duluth".
SQL> select cid
from customers
where discnt <all (select discnt
from customers
where city = 'Duluth');
CID
----
c006
The general form of exists and not exists predicates:
exists (sub-select)
not exists (sub-select)
(10) Get names of customers who have placed an order
through agent "a05".
SQL> select c.cname
from customers c
where exists (select *
from orders x
where c.cid = x.cid and
x.aid = 'a05');
CNAME
-------------
Tiptop
Basics
An equivalent SQL query without exists predicate:
SQL> select c.cname
from customers c, orders x
where c.cid = x.cid and x.aid = 'a05';
(11) Get cid values of customers who have placed an
order for both "p01" and "p07".
SQL> select cid
from orders x
where pid = 'p01' and
exists (select *
from orders
where cid = x.cid and
pid = 'p07');
CID
----
c001
c001
c006
c006
An equivalent SQL query without exists predicate:
SQL> select x.cid
from orders x, orders y
where x.pid = 'p01' and
x.cid = y.cid and
y.pid = 'p07';
(12) Get names of customers who do not place orders
through agent "a05".
SQL> select c.cname
from customers c
where not exists (select *
from orders x
where c.cid = x.cid and
x.aid = 'a05');
CNAME
-------------
Allied
ACME
ACME
Two alternate solutions (using not in and <>all):
SQL> select c.cname
from customers c
where c.cid not in (select cid
from orders
where aid = 'a05');
SQL> select c.cname
from customers c
where c.cid <>all (select cid
from orders
where aid = 'a05');
(13) Too many ways to write an SQL query!!
Get city names of customers who have placed
an order for "p01".
SQL> select distinct city
from customers
where cid in (select cid
from orders
where pid = 'p01');
SQL> select distinct city
from customers
where cid =any (select cid
from orders
where pid = 'p01');
SQL> select distinct city
from customers c
where exists (select *
from orders
where cid = c.cid and
pid = 'p01');
SQL> select distinct city
from customers c, orders x
where x.cid = c.cid and x.pid = 'p01';
SQL> select distinct city
from customers c
where 'p01' in (select pid
from orders
where cid = c.cid);