【原】Oracle中Left Outer Join和外關聯(+)的區別

CREATE TABLE departments

(

 depID   NUMBER(38,0),

 depName VARCHAR2(20),

 delFlag NUMBER(1,0)

);

CREATE TABLE employees

(

 empID   NUMBER(38,0),

 empName VARCHAR2(20),

 depID   NUMBER(38,0),

 delFlag NUMBER(1,0)

);

INSERT INTO departments VALUES(1,'Finacle',0);

INSERT INTO departments VALUES(2,'Marketing',0);

INSERT INTO departments VALUES(3,'HR',1);

INSERT INTO departments VALUES(4,'IT',0);

INSERT INTO employees VALUES(1,'wbq',1,0);

INSERT INTO employees VALUES(2,'czh',2,0);

INSERT INTO employees VALUES(3,'chh',1,0);

INSERT INTO employees VALUES(4,'wal',2,0);

INSERT INTO employees VALUES(5,'ddd',3,0);

COMMIT;

--列出部門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;