---

The outer join operators of SQL-99

---

• Outer Joins
  • Recall:

    An outer join is an join operation that perserves all tuples from one or more of its input relations

    ---

  • Inner join:

    select ssn, fname, lname from employee; SSN FNAME LNAME --------- ---------- ---------- 123456789 John Smith 333445555 Frank Wong 987654321 Jack Wallace 999887777 Alice Miller 666884444 John Doe 453453453 Joyce English 987987987 Jake Jones 888665555 James Borg select * from dependent; ESSN NAME S BDATE RELATIONSH --------- ---------- - ---------- ---------- 333445555 Alice F 05-APR-76 DAUGHTER 333445555 Theodore M 25-OCT-73 SON 333445555 Joy F 03-MAY-48 SPOUSE 987654321 Abner M 29-FEB-32 SPOUSE 123456789 Micheal M 01-JAN-78 SON 123456789 Alice F 31-DEC-78 DAUGHTER 123456789 Elizabeth F 05-MAY-57 SPOUSE select ssn, fname, lname, name from employee join dependent on ssn=essn SSN FNAME LNAME NAME --------- ---------- ---------- ---------- 123456789 John Smith Elizabeth 123456789 John Smith Alice 123456789 John Smith Micheal 333445555 Frank Wong Joy 333445555 Frank Wong Theodore 333445555 Frank Wong Alice 987654321 Jack Wallace Abner

    Notice that the inner join does not select non-matching tuples

    ---

    ---

  • The outer join operation will output non-matching tuples also:

    The non-matching tuples will have NULL values in other attributes

    ---

    ---

  • There are THREE different flavors of outer joins :

    Left Outer Join: preserves ALL tuples of the left operand (relation) Right Outer Join preserves ALL tuples of the right operand (relation) Full Outer Join preserves ALL tuples of the both operands (relations)

  ---

  ---

  ---

  ---

• Left Outer Join
  • Syntax:

    r1 LEFT [OUTER] JOIN r2 ON join-condition

  • Example:

    Employee: SSN FNAME LNAME --------- ---------- ---------- 123456789 John Smith 333445555 Frank Wong 987654321 Jack Wallace 999887777 Alice Miller (Non-matching tuples listed below) 666884444 John Doe 453453453 Joyce English 987987987 Jake Jones 888665555 James Borg Dependent: ESSN NAME S BDATE RELATIONSH --------- ---------- - ---------- ---------- 333445555 Alice F 05-APR-76 DAUGHTER 333445555 Theodore M 25-OCT-73 SON 333445555 Joy F 03-MAY-48 SPOUSE 987654321 Abner M 29-FEB-32 SPOUSE 123456789 Micheal M 01-JAN-78 SON 123456789 Alice F 31-DEC-78 DAUGHTER 123456789 Elizabeth F 05-MAY-57 SPOUSE select ssn, fname, lname, name from employee left outer join dependent on ssn=essn SSN FNAME LNAME NAME --------- ---------- ---------- ---------- 333445555 Frank Wong Alice 333445555 Frank Wong Theodore 333445555 Frank Wong Joy 987654321 Jack Wallace Abner 123456789 John Smith Micheal 123456789 John Smith Alice 123456789 John Smith Elizabeth 999887777 Alice Miller (NULL) 666884444 John Doe (NULL) 987987987 Jake Jones (NULL) 888665555 James Borg (NULL) 453453453 Joyce English (NULL)

  ---

  ---

  ---

  ---

• Right Outer Join
  • Syntax:

    r1 RIGHT [OUTER] JOIN r2 ON join-condition

  ---

  ---

  ---

  ---

• Full Outer Join
  • SQL 99 syntax:

    r1 FULL [OUTER] JOIN r2 ON join-condition

    ---

  • However:

    Full outer join is not available in MySQL !!!

    But:

    You can compute (find) the full outer join in MySQL as follows: r1 LEFT JOIN r2 ON join-condition union r1 RIGHT JOIN r2 ON join-condition

  ---

  ---

  ---

  ---

• A note on Outer Join
  • NULL values is a royal pain in the butt to handle in the formulation of a query

  • Because outer joins can return NULL values in some attributes, the query techniques that you have learned may failed to obtain the correct tuples (it may return extraneous tuples )

    ---

    I personally avoid NULL values like I would avoid the plague....

  • If you use them and get burn; don't blame me.....