sql - How to find LEFT OUTER JOIN or RIGHT OUTER JOIN with ORACLE JOIN (+)

Question

I am confused with finding left outer join and right outer join properly with Oracle join (+) sign. Check this vs this. I feel both contradict. What I understand is, First link says if the (+) sign is in right hand side, it will be Right Outer Join.

Whereas with second link, my understanding is totally wrong.

Please clarify how to find the right and left outer join properly with an example?

Answer 1

Please clarify how to find the right and left outer join properly with an example

I will give a try to show the difference between Oracle outer join syntax and the ANSI/ISO Syntax.

LEFT OUTER JOIN -

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id = d.department_id(+);

SELECT e.last_name,
  d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

RIGHT OUTER JOIN -

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id(+) = d.department_id;

SELECT e.last_name,
  d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

FULL OUTER JOIN -

Before the native support of hash full outerjoin in 11gR1, Oracle would internally convert the FULL OUTER JOIN the following way -

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id = d.department_id(+)
UNION ALL
SELECT NULL,
  d.department_name
FROM departments d
WHERE NOT EXISTS
  (SELECT 1 FROM employees e WHERE e.department_id = d.department_id
  );

SELECT e.last_name,
  d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);

Have a look at this.