Nested Subqueries in SQL
By Kenneth Cheung
CS 157A Section 2
Professor Lee
Terms
For a query,
in- tests for set membership
select- chooses values
not in - absence of set membership
Example 1:
Find all customers who have both a
loan and an account at a bank.
Start with the bank account
(select customer_name
from depositor)
Example 1
Find the list of customers who also have a loan
account. combine using the outer select.
select distinct customer_name
from borrower
where customer_name in (select
customer_name
from depositor)
Example 2: Find all customers who
have a loan from the bank but not an
account.
selectdistinct customer_name
from borrower
where customer_name not in (select
customer_name
from depositor)
Example 2: Find the names of all
customers who are not Smith nor Jones
selectdistinct customer_name
from borrower
where customer_name not in (‘Smith’, ’Jones’)
Example 3: Find the names of all
branches that have assets greater than
those of at least one in Brooklyn
Set comparison using comparing operators
Use the rename operator as to compare
elements in the same table
select distinct T.branch_name
from branch as T, branch as S
where [Link] > [Link] and S.branch_city
= ‘Brooklyn’
Example 4: Another way using the
“some” operator
some- at least one
select branch_name
from branch
where assets > some (select assets
from branch
where branch_city = ‘Brooklyn’)
Example 5: Find all branches that have
an asset value greater than that of
each branch in Brooklyn.
= some - the same as in
<> all - the same as not in
select branch_name
from branch
where assets > all (select assets
from branch
where branch_city = ‘Brooklyn’)
Example 6: Find the branch with the
highest average balance.
SQL cannot compose of aggregate functions
(function of a function), so it must be written in a
certain way.
select branch_name
from account
group by branch_name
having avg (balance) >= all (select avg (balance)
from account
group by branch_name)
Example 7: find all customers who
have both a loan and an account at the
bank.
exists - returns true if the argument in it is not
empty
select customer_name
from borrower
where exists (select *
from depositor
where depositor.customer_name =
borrower.customer_name)
Example 8:
not exists- superset operation.
To show that relation A contains relation B,
use not exists (B except A)
