Database Reference
In-Depth Information
Equi-joins
An
equi-join
is a special type of theta join where only equality operators are used in the join condition. The following
two queries are examples:
SELECT emp.ename, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
SELECT emp.ename, dept.dname
FROM emp JOIN dept ON emp.deptno = dept.deptno
Self-joins
A
self-join
is a special type of theta join where a table is joined to itself. The following two queries are examples of this.
Notice that the
emp
table is referenced twice in the
FROM
clause:
SELECT emp.ename, mgr.ename
FROM emp, emp mgr
WHERE emp.mgr = mgr.empno
SELECT emp.ename, mgr.ename
FROM emp JOIN emp mgr ON emp.mgr = mgr.empno
Outer Joins
An
outer join
extends the result set of a theta join. In fact, with an outer join, all rows of one table (the
preserved table
)
are returned even if no matching value is found in the other table. The value
NULL
is associated with the returned
columns of the table that don't contain matching rows. For instance, the queries in the previous section (self-joins)
don't return all rows of the
emp
table because the employee
KING
, who is the president, has no manager. To specify an
outer join with the legacy syntax, an Oracle extension (based on the operator
(+)
) must be used. The following query
is an example:
SELECT emp.ename, mgr.ename
FROM emp, emp mgr
WHERE emp.mgr = mgr.empno(+)
To specify an outer join with the new syntax, several possibilities exist. For example, the following two queries are
equivalent to the previous one:
SELECT emp.ename, mgr.ename
FROM emp LEFT JOIN emp mgr ON emp.mgr = mgr.empno
SELECT emp.ename, mgr.ename
FROM emp mgr RIGHT JOIN emp ON emp.mgr = mgr.empno