1. LEFT OUTER JOIN:左外關(guān)聯(lián)
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);
等價(jià)于
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id(+);
結(jié)果為:所有員工及對(duì)應(yīng)部門的記錄，包括沒有對(duì)應(yīng)部門編號(hào)department_id的員工記錄。
2. RIGHT OUTER JOIN:右外關(guān)聯(lián)
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);
等價(jià)于
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+)=d.department_id;
結(jié)果為:所有員工及對(duì)應(yīng)部門的記錄，包括沒有任何員工的部門記錄。
3. FULL OUTER JOIN:全外關(guān)聯(lián)
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);
結(jié)果為:所有員工及對(duì)應(yīng)部門的記錄，包括沒有對(duì)應(yīng)部門編號(hào)department_id的員工記錄和沒有任何員工的部門記錄。
