oracle先关联后条件,Oracle中Left Join和外关联(+)的区别附带多条件查询

--列出部门ID为3的部门和员工信息，不管该部门是否有员工

SELECT d.depID,d.depName,e.empName

FROM departments d

LEFT OUTER JOIN employees e

ON d.depID =

e.depID

WHERE d.depID =3

ORDER BY d.depID;

--和上面等效

SELECT d.depID,d.depName,e.empName

FROM departments d, employees

e

WHERE d.depID = e.depID(+)

AND d.depID

=3

ORDER BY d.depID;

--该例子可能不是很恰当，d.depID=3表示首先进行全左连接，然后查找depID=3的纪录，如果该部门中找不到对应的员工，则该部门员工则为NULL，同时都显示部门信息。

SELECT d.depID,d.depName,e.empName d.delflag

FROM departments d

LEFT OUTER JOIN employees e

ON d.depID =

e.depID AND e.depID=3

ORDER BY d.depID;

--和上面不等价，差别在于能够找到无对应员工的部门和有对应员工的部门，却找不到该部门无员工的部门。

SELECT d.depID,d.depName,e.empName,d.delflag

FROM departments d, employees

e

WHERE d.depID = e.depID(+)

AND (e.depID=3

or e.depID is NULL)

ORDER BY d.depID;