Oracle 9i introduced joins like RIGHT OUTER JOIN (ROJ), LEFT OUTER JOIN (LOJ) and FULL OUTER JOIN (FOJ). I would say there is an advantage of FOJ. But what is the advantage of ROJ and LOJ over the (+) in previous versions?
In Oracle 9i, Oracle Corp. introduced the RIGHT OUTER JOIN and LEFT OUTER JOIN syntax. Prior to Oracle 9i, one could still perform right and left outer joins by placing the '(+)' characters on the appropriate side of the equals sign in the WHERE clause on the join columns. The new statements do not provide any new functionality that did not exist before. But they do make Oracle 9i more ANSI-SQL compliant. One of the complaints of anti-Oracle vendors was that it didn't support the ANSI-SQL standards. Now, a SQL statement written for SQL Server that does an outer join will work on Oracle without further modification.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.