from fi_cont_main m
,fi_contract c,
bd_cubasdoc jf
,bd_cubasdoc yf,
fi_type_para ty
,BD_CURRTYPE ct
,fi_type_para lm
,fi_ratechange_d rcd
,fi_rate_d rd 人家需要连接的表直接写在from后面了,然后在where中写成这样where m.pk_id = c.pk_parent
and c.jiafangunit = jf.pk_cubasdoc
and c.yifangunit = yf.pk_cubasdoc
and m.loanclass=ty.systemname
and c.currencycode = ct.pk_currtype
and m.loanmode = lm.systemname
and c.ratecode = rcd.pk_parent(+)
and rcd.pk_id = rd.pk_parent(+)
尤其是rd.pk_parent(+) (+)什么意思啊,他这种写法和我用left join right join连有什么区别吗
,fi_contract c,
bd_cubasdoc jf
,bd_cubasdoc yf,
fi_type_para ty
,BD_CURRTYPE ct
,fi_type_para lm
,fi_ratechange_d rcd
,fi_rate_d rd 人家需要连接的表直接写在from后面了,然后在where中写成这样where m.pk_id = c.pk_parent
and c.jiafangunit = jf.pk_cubasdoc
and c.yifangunit = yf.pk_cubasdoc
and m.loanclass=ty.systemname
and c.currencycode = ct.pk_currtype
and m.loanmode = lm.systemname
and c.ratecode = rcd.pk_parent(+)
and rcd.pk_id = rd.pk_parent(+)
尤其是rd.pk_parent(+) (+)什么意思啊,他这种写法和我用left join right join连有什么区别吗
* 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 Database 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 ed with the (+) operator.
* A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
* A WHERE condition cannot use the IN comparison condition to compare a column ed with the (+) operator with an expression.
* A WHERE condition cannot compare any column ed 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 returns 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. Please refer to SELECT for the syntax for an outer join.
对于外连接,Oracle中可以使用“(+)”来表示,9i可以使用LEFT/RIGHT/FULL OUTER JOIN,1. LEFT OUTER JOIN:左外关联 SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id); 等价于 SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id=d.department_id(+); 结果为:所有员工及对应部门的记录,包括没有对应部门编号department_id的员工记录。 2. RIGHT OUTER JOIN:右外关联 SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id); 等价于 SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+)=d.department_id; 结果为:所有员工及对应部门的记录,包括没有任何员工的部门记录。 3. FULL OUTER JOIN:全外关联 SELECT e.last_name, e.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id); 结果为:所有员工及对应部门的记录,包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。