foreign und leere werte

[Lutz Hieronymus]

im mssql kann ich problemlos eine
foreign beziehung auf leere zeichenketten
erstellen.
die foreign die im mssql klappt funtioniert
im oracle nicht.
sicherlich weil oracle eine leere zeichenkette als null interpretiert. als konzequenz müsste
ich ja alle leeren zeichenketten vorbelegen.
gibts nicht eine variante für oracle, die wie mssql funktioniert?

[Stefan Frank]

Hallo,

nimm einen Outer Join:

Outer Joins
An outer join extends the result of a simple join. An outer join returns all rows that
satisfy the join condition and those rows from one table for which no rows from the
other satisfy the join condition. Such rows are not returned by a simple join. To
write a query that performs an outer join of tables A and B and returns all rows
from A, apply the outer join operator (+) to all columns of B in the join condition.
For all rows in A that have no matching rows in B, Oracle returns null for any select
list expressions containing columns of B.

+Outer join queries are subject to the following rules and restrictions:
The (+) operator can appear only in the WHERE clause or, in the context of leftcorrelation
(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, you must use the (+) operator
in all of these conditions. If you do not, Oracle will return only the rows
Queries and Subqueries
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 can be applied only to a column, not to an arbitrary expression.
However, an arbitrary expression can contain a column 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 operator 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, 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.

SELECT ename, dname
FROM emp, dept
WHERE dept.deptno =
emp.deptno(+);

Gruss

Stefa