next up previous
Next: Data-Definition Language Up: Joined Relations Previous: Examples

Join types and conditions

  1. Each variant of the join operations in SQL-92 consists of a join type and a join condition.
  2. Join types: inner join, left outer join, right outer join, full outer join.

    The keyword inner and outer are optional since the rest of the join type enables us to deduce whether the join is an inner join or an outer join.

    SQL-92 also provides two other join types:

    1. cross join: an inner join without a join condition.
    2. union join: a full outer join on the ``false'' condition, i.e., where the inner join is empty.
  3. Join conditions: natural, on predicate, using tex2html_wrap_inline1844 .

    The use of join condition is mandatory for outer joins, but is optional for inner joins (if it is omitted, a Cartesian product results).

  4. Ex. A natural full outer join:

     loan natural full outer join borrower

    using (loan#)

    Figure 4.5:   Result of loan natural full outer join borrower using (loan#).

  5. Ex. Find all customers who have either an account or a loan (but not both) at the bank.

     select cname

    from (natural full outer join borrower)

    where account# is null or loan# is null

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