When the columns of a table are outer joined, this tells the database to retrieve rows even if data is not found. The plus symbol (+) is used to denote an outer join condition, as shown in the following example:
SELECT d.name,
a.city,
e.last_name,
e.first_name
FROM emp e,
dept d,
addresses a
WHERE d.dept_no(+) = e.dept_no
AND a.adrs_id = d.adrs_id
ORDER BY d.name,e.last_name,e.first_name;
If the president of the company was never assigned a department, his name would never be retrieved in previous examples because his department number would be null. The outer join would cause all rows to be retrieved even if there is not a match for dept_no.
Outer joins are effective but will make the query perform slower. You might need to rewrite the query if you need to improve performance.
SELECT d.name,
a.city,
e.last_name,
e.first_name
FROM emp e,
dept d,
addresses a
WHERE d.dept_no(+) = e.dept_no
AND a.adrs_id = d.adrs_id
ORDER BY d.name,e.last_name,e.first_name;
If the president of the company was never assigned a department, his name would never be retrieved in previous examples because his department number would be null. The outer join would cause all rows to be retrieved even if there is not a match for dept_no.
Outer joins are effective but will make the query perform slower. You might need to rewrite the query if you need to improve performance.