- Syntax for union:
(sub-select) union (sub-select) (sub-select) union all (sub-select)
- No direct equivalent for division operation.
(1) Get cities in which customers or agents are
located.
SQL> select city
from customers
union
select city
from agents;
CITY
--------------------
Dallas
Duluth
Kyoto
New York
Newark
Tokyo
The following will not eliminate duplicates:
SQL> select city
from customers
union all
select city
from agents;
CITY
--------------------
Duluth
Dallas
Dallas
Duluth
...
11 rows selected.
(2) Get cid values of customers who place orders
with ALL agents in "New York".
Get cid values of customers such that
(the set of agents from "New York"
through whom the customer has NOT
placed an order) is EMPTY.
SQL> select c.cid CID
from customers c ----
where not exists c001
(select *
from agents a
where a.city = 'New York' and
not exists (select *
from orders x
where x.cid = c.cid and
x.aid = a.aid));
(3) Get aid values of agents from "New York" or "Duluth" who
place orders for ALL products priced over one dollar.
Get aid values of agents from "New York" or "Duluth" such
that (the set of products priced over one dollar
that the agent has NOT ordered) is EMPTY.
SQL>select a.aid AID
from agents a ---
where (a.city in ('New York','Duluth')) and a05
not exists (select p.pid
from products p
where p.price > 1.00 and
not exists (select *
from orders x
where x.pid = p.pid
and x.aid = a.aid));
(4) Get cid values for customers who order ALL products
ordered by customer "c006".
Get cid values of customers (call them C) such that
(the set of products ordered by customer
"c006" and NOT ordered by the customer C) is EMPTY
SQL> select cid CID
from customers c ----
where not exists c001
(select p.pid c006
from products p
where p.pid in (select pid
from orders x
where x.cid = 'c006') and
not exists (select *
from orders y
where y.pid = p.pid and
y.cid = c.cid));
A variant is:
SQL> select cid
from customers c
where not exists
(select z.pid
from orders z
where z.cid = 'c006' and
not exists
(select *
from orders y
where y.pid = z.pid and
y.cid = c.cid));
(5) Get pid values of products that are supplied to
all customers in "Duluth".
Get pid values of products such that
(the set of customers from "Duluth"
to whom the product is NOT supplied) is EMPTY.
SQL> select pid PID
from products p ---
where not exists p01
(select c.cid
from customers c
where c.city = 'Duluth' and
not exists
(select *
from orders x
where x.pid = p.pid and
x.cid = c.cid));