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
 
Search WWH ::




Custom Search