SQL: Structured Query Language - Oracle 10g Database Administration Concepts and Implementation Made Simple - page 40

What are SQL Joins?
Joins help in extracting data from two or more tables based on
some condition specified in the WHERE clause. The very
important point to note over here is that Joins are only at the
query level. It means the output that you see as a result of
statement containing any form of “Join”, is only for that instance
and it's in the volatile memory (RAM). There are following
different kinds of Joins and each having its own application. Out
of these, two are having extremely high importance viz., Equi-
Join and Outer-Join.
Self Joins
Cartesian Products
Equijoins
Outer Joins
SELECT statements containing Joins should not be confused
with “Primary/Foreign” key relationship between the tables
commonly known as Linking Tables which we will discuss later
in this chapter. Just keep in mind whether you have established a
primary/foreign key relationship between the tables or not, you
can still run the SELECT statements containing joins against
those tables.
Self Join
A self join is a join of a table to itself. The same table appear
twice after the FROM clause. We have used e1 and e2 as two
different aliases for the same table. It can be visualized by
looking at the following example.
Example:
SELECT e1.ename||' works for '||e2.ename "Result"
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno;