Oracle9i New Features for DBA

Oracle9i New Feature Series: ANSI Style Outer Join Syntax

If you’re one of those people who always get confused on where to put the (+) for an outer join, here is relief for you. Oracle9i supports the ANSI style outer join syntax. You can now use the English like meaningful keywords and even do an outer join from both tables (full join).

The following are excerpts from Oracle Documentation – SQL Reference Guide

New Outer Join Syntax

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

	To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the `LEFT` [`OUTER`] `JOIN` syntax in the `FROM` clause, or apply the outer join operator (+) to all columns of B in the join condition in the `WHERE` clause. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B.
	To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the `RIGHT` [`OUTER`] `JOIN` syntax in the `FROM` clause, or apply the outer join operator (+) to all columns of A in the join condition in the `WHERE` clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
	To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the `FULL` [`OUTER`] `JOIN` syntax in the `FROM` clause.

Oracle Corporation recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause join syntax:

	You cannot specify the (+) operator in a query block that also contains `FROM` clause join syntax.
	The (+) operator can appear only in the `WHERE` clause or, in the context of left-correlation (that is, when specifying the `TABLE` clause) in the `FROM` clause, and can be applied only to a column of a table or view.
	If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
	The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
	You cannot use the (+) operator to outer-join a table to itself, although self joins are valid. For example, the following statement is not valid:

-- The following statement is not valid:

SELECT employee_id, manager_id
FROM employees
WHERE employees.manager_id(+) = employees.employee_id;

However, the following self join is valid:

SELECT e1.employee_id, e1.manager_id, e2.employee_id
FROM employees e1, employees e2
WHERE e1.manager_id(+) = e2.employee_id;

	The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
	A condition containing the (+) operator cannot be combined with another condition using the `OR` logical operator.
	A condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
	A condition cannot compare any column marked with the (+) operator with a subquery.
	If the `WHERE` clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle will return only the results of a simple join.
	In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C.

Using Outer Joins: Examples

The following example uses a left outer join to return the names of all departments in the sample schema hr, even if no employees have been assigned to the departments:

SELECT d.department_id, e.last_name
FROM departments d LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
ORDER BY d.department_id;

DEPARTMENT_ID LAST_NAME
------------- -------------------------
10 Whalen
20 Hartstein
20 Fay
30 Raphaely
...
250
260
270

Users familiar with the traditional Oracle outer joins syntax will recognize the same query in this form:

SELECT d.department_id, e.last_name
FROM departments d, employees e
WHERE d.department_id = e.department_id(+)
ORDER BY d.department_id;

The left outer join returns all departments, including those without any employees. The same statement with a right outer join returns all employees, including those not yet assigned to a department:

SELECT d.department_id, e.last_name
FROM departments d RIGHT OUTER JOIN employees e
ON d.department_id = e.department_id
ORDER BY d.department_id;

DEPARTMENT_ID LAST_NAME
------------- -------------------------
...
110 Higgins
110 Gietz
Grant
Zeuss

It is not clear from this result whether employees Grant and Zeuss have department_id NULL, or whether their department_id is not in the departments table. To determine this requires a full outer join:

SELECT d.department_id as d_dept_id,
e.department_id as e_dept_id, e.last_name
FROM departments d FULL OUTER JOIN employees e
ON d.department_id = e.department_id
ORDER BY d.department_id;

D_DEPT_ID E_DEPT_ID LAST_NAME
---------- ---------- -------------------------
...
110 110 Gietz
110 110 Higgins
...
260
270
999 Zeuss
Grant

Because the column names in this example are the same in both tables in the join, you can also use the common column feature (the USING clause) of the join syntax, which coalesces the two matching columns department_id. The output is the same as for the preceding example:

SELECT department_id AS d_e_dept_id, e.last_name
FROM departments d FULL OUTER JOIN employees e
USING (department_id)
ORDER BY department_id;

D_E_DEPT_ID LAST_NAME
----------- -------------------------
...
110 Higgins
110 Gietz
...
260
270
Grant
Zeuss

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP,
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.