Oracle join (+) notation

Oracle join (+) notation

select author_last_name, book_key
from author left outer join book_author using (author_key)
order by author_last_name;   

In the example above, the AUTHOR table is on the left, and we are using a left outer join, so we get all the rows in the AUTHOR table and the matching rows in the book_author table.  Notice that both authors clark and mee now are listed, and the book_key column is NULL.  In the standard Oracle format, outer joins can be confusing.  Below is the same query in the standard Oracle format.

select
  author_last_name,
  book_key
from
  author,
  book_author
where
  author.author_key = book_author.author_key(+)
order by author_last_name;

Notice the (+) in the WHERE clause.  This indicates a left outer join.  If we were using a right outer join, the WHERE clause would be:

author.author_key(+) = book_author.author_key

Advantages of JOIN notation

–The AND clause blocks the outer join
SELECT A.ml_street, L.ml_name, L.f_tr_status
FROM EN_TR_COMP_ADRESS A, EN_TR_LOCATION L
WHERE l.f_tr_comp_adress = a.row_id(+)
AND L.f_tr_status IS NULL

–The AND clause blocks the outer join (JOIN equivalant)
SELECT A.ml_street, L.ml_name, L.f_tr_status
FROM EN_TR_COMP_ADRESS A LEFT JOIN EN_TR_LOCATION L
ON l.f_tr_comp_adress = a.row_id
WHERE L.f_tr_status IS NULL

–The AND clause does not block the outer join (this notation is only possible with JOIN)
SELECT A.ml_street, L.ml_name, L.f_tr_status
FROM EN_TR_COMP_ADRESS A LEFT JOIN EN_TR_LOCATION L
ON l.f_tr_comp_adress = a.row_id AND L.f_tr_status IS NULL

Advertisements

%d bloggers like this: