1、如何理解这样的“连接”2、标准连接就不如“(+)”吗？

rake · 发表于 2005-4-6 18:33

一、问题起源。
疑惑起源于看到一个SQL里有这样的一段表达式
AND A.CARD_ID = B.CARD_ID(+)
AND B.DEPT_ID(+)[/COLOR]  IS NULL
AND B.ORGAN_ID(+)[/COLOR] = '1'

“B.DEPT_ID(+) IS NULL”和“B.ORGAN_ID(+) = '1'”，怎么这里面也会有“(+)”加号呢？

二、实验。
我做了实验。假设A、B两个表。
A.TITLE B.TITLE
------- -------
1          1
2          2
3          4

1、常见的外连接（左连接）
SQL> select * from A,B
  2  where A.title = B.title(+);
TITLE TITLE
----- -----
1    1
2    2
3

2、我看不懂的写法。B.title(+) = 1
SQL> select * from A,B
  2  where A.title = B.title(+);
  3  and B.title(+) = 1;

TITLE TITLE
----- -----
1    1[/COLOR]
2
3

3、再来一种写法，B.title = 1，就开始搞脑子了。
SQL> select * from A,B
  2  where A.title = B.title(+);
  3  and B.title = 1;

TITLE TITLE
----- -----
1    1
大家能谈谈 2和3的执行顺序吗？从逻辑上我们怎么去一步一步的推断执行的结果呢？

三、又想到了标准SQL。
接着问题又来了。
现在都推荐写标准SQL语句了。9i也支持SQL99标准的连接写法。
看，干净利落的左连接。
SQL> select t1.title,t2.title
  2  from test_join_1 t1 LEFT OUTER JOIN test_join_2 t2
  3    ON (t1.title=t2.title);

TITLE TITLE
----- -----
1    1
2    2
3
问题是，怎么使用标准SQL来达到前面实验中第2点所表现的结果呢？
当然，可以通过decode函数。
SQL> select t1.title,decode(t2.title,1,t2.title,NULL)
  2  from test_join_1 t1 LEFT OUTER JOIN test_join_2 t2
  3  ON (t1.title=t2.title);

TITLE DECODE(T2.TITLE,1,T2.TITLE,NUL
----- ------------------------------
1                            1
2
3
不过decode函数好像也不是标准函数。
所以再请大家谈谈有何见解？是不是用标准的SQL就是不能了？

谢谢。

rake · 发表于 2005-4-12 22:45

rollingpig · 发表于 2005-4-13 08:57

B.title(+) = 1
==>
b.title = 1 or b.title is null

A.title = B.title(+)
and B.title = 1
==>
a.title = b.title or exist a.title but  b.title does not exist
and  B.title = 1
结合起来
等于a.title = b.title

A.title = B.title(+)
and B.title(+) = 1
a.title = b.title or exist a.title but  b.title does not exist
and  B.title = 1 or b.title is null
相当于
(a.title = b.title and b.title = 1) or ( not exist b.title )

wyq21973 · 发表于 2005-4-13 09:42

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:

n 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.

n 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 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.

n 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.

n A condition containing the (+) operator cannot be combined with another
condition using the OR logical operator.

n A condition cannot use the IN comparison operator to compare a column marked with the (+) operator with an expression.

n 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.

1、如何理解这样的“连接”2、标准连接就不如“(+)”吗？

自己顶一下