Tags

, , , , , , ,

this is one of the frequently asked questions about the use of “+” operator in Oracle join syntax. For beginners it is usually a confusion to interpret the different syntax.

One of the good reasons to use ANSI syntax over the old Oracle join syntax is that, there are nil chances of accidentally creating a cartesian product. With more number of tables, there is a chance to miss an implicit join with older Oracle join syntax, however, with ANSI syntax you cannot miss any join as you must explicitly mention them.

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);

Hope it helps!