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 .

   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