next up previous
Next: Set Comparison Up: Nested Subqueries Previous: Nested Subqueries

Set Membership

  1. We use the in and not in operations for set membership.

     select distinct cname

    from borrower

    where cname in

    (select cname from account where bname=``SFU'')

  2. Note that we can write the same query several ways in SQL.
  3. We can also test for more than one attribute:

     select distinct cname

    from borrower, loan

    where = and bname=``SFU''

    and (bname, cname) in

    (select bname, cname from account, depositor where depositor.account# = account.account#)

    This finds all customers who have a loan and an account at the SFU branch in yet another way.

  4. Finding all customers who have a loan but not an account, we can use the not in operation.

Osmar Zaiane
Fri May 22 19:39:12 PDT 1998