 |
|
Oracle Tips by Burleson |
Self Join
A self join is used to join a table to itself,
and it is commonly used when you have a table with dates and you want
to compare one date to another within the same table.
Visually, it sometimes helps to look at the table
as two identical tables (A and B). I am joining A to B, it just
happens to be the same table. Because the table is used twice in the
query, you must alias the table, and of course, you cannot use a
natural join. In the example below, I want a list of employees and
their managers. In the EMP table, each employee is identified by an
emp_key. The employee’s
manager is in the manager column and it contains the manager’s
emp_key.
SQL> select
2 b.emp_last_name EMPLOYEE,
3 a.emp_last_name MANAGER
4 from
5 emp a right outer join emp b on (a.emp_key
= b.manager);
EMPLOYEE MANAGER
------------------------------
------------------------------
brannigan king
coleman king
baker king
johnson king
levender king
tokheim king
linus king
korn king
jackson king
king
10 rows selected
As you can see, King is the manager for each of
the employees, and King does not have a manager assigned. I used an
outer join so that King would also be listed as an employee, even
though he has no manager. Otherwise, King would not have been
listed. I join the emp table
to itself, joining where the emp_key
matched the manager.
Multi-table Joins
Multi-table joinscan become confusing and this is where the ANSIformat
really shines. By keeping all the join information out of the WHERE
clause, it is much easier to read as the query becomes more
complicated. Using the ANSI format, you can also outer join multiple
tables with multiple outer joins. That was not allowed in the
standard Oracle format.
SQL> select
2 author_last_name c1,
3 book_title c2
4 from
5 author full outer join book_author using
(author_key)
6 full outer join book using (book_key)
7 order by author_last_name;
Author Title
-------------------------
-----------------------------------
clark
hester windows success
hester pay no taxes and go
to jail
hester oracle9i sql tuning
jeckle piano greats
jeckle the fall of microsoft
jeckle operations research
theory
jones windows success
jones never eat meat
jones was george washington
feeble?
jones cooking light
mee
petty the willow weeps no
more
shagger The zen of auto
repair
shagger writers market
shagger bears are people too
shagger how to housebreak
your horse
shagger was george washington
feeble?
shagger non violins in the
workplace
smith zero loss finance
smith managing stress
weaton piano greats
weaton never eat meat
weaton UNIX for experts
weaton reduce spending the
republican way
withers DOS for dummies
withers never eat meat
fired - now what
the far side of the
goon
29 rows selected.
In the example above, I joined three tables using
full outer joins. This allowed me to include both the books without
authors and the authors without books in my report. Note: I had to
remove the book_keys that did
not have author_keys from the
book_author table for this
example.
When joining multiple tables, the joins are
performed from left to right. Each successive table on the right can
use the columns resulting from the joins to its left. You can also
mix the use of the ON and USING
clause in your joins.
We now have the ability to link our tables and
retrieve information from multiple tables. But what if you do not
want to use the entire table? A subquery gives use the ability to
create a subset of data, that is then used in our main query.
The above text is
an excerpt from:
Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
by
John Garmany
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
