| Team Fly | |
ID DESC1 ID DESC2
---------- ----- ---------- -----
456 EFGH 456 ZZZZ
123 ABCD 789 MMMM
The three examples in this project show an alternate way of performing outer joins using ANSI SQL. Our first join, the right outer join, returns all of the rows from the table listed on the right side of the from clause, TEMP1, regardless of whether or not they match to a row from the other table, TEMP2.
The second example switches the logic. The table on the left, TEMP2, returns all rows with a left outer join specified as natural left join.
Our final example introduces the full outer join concept available with ANSI SQL. In this case, all rows are returned from each table regardless of whether or not a match was made.
A self-join is used for a relationship within a single table. Rows are joined back to the same table instead of joining them to a related second table as we have seen with the many CUSTOMERS and SALES tables examples throughout this chapter.
A common example involves hierarchical relationships where all of the records and related records are stored within the same table. A family tree is one such hierarchy that best illustrates the self-join. Let's take a look at the FAMILY table that we have created for this concept:
SQL> desc family Name Null? Type ------------------------------------------ -------- -------------- NAME NOT NULL CHAR(10) BIRTH_YEAR NOT NULL NUMBER(4) FATHER CHAR(10)
The table contains columns for a person's name and birth year as well as their father's names. The fathers each have their own row in the table with their respective birth years and names. This table could be filled out with every known relationship in the family tree. For our example, Isaac, born in 1894, has a son, Edward, who was born in 1935. Edward has three children: Mark, born in 1961; Beth, born in 1964; and Cari, born in 1966. The following example first takes a look at all of the records in the table followed by our hierarchical self-join example:
SQL> select *from family; NAME BIRTH_YEAR FATHER ---------- ---------- ---------- Isaac 1894 Edward 1935 Isaac
| Team Fly | |