Oracle “(+)” Operator

by

That’s Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn’t standardize OUTER joins.

The query would be re-written in ANSI-92 syntax as:

   SELECT ...
     FROM a
LEFT JOIN b ON b.id = a.id

This link is pretty good at explaining the difference between JOINs.

It should also be noted that even though the (+) works, Oracle recommends not using it:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

More Related Contents:

  1. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  2. How can I do a FULL OUTER JOIN in MySQL?
  3. Difference between Oracle’s plus (+) notation and ansi JOIN notation?
  4. Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?
  5. Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL [duplicate]
  6. How do I convert a “legacy” left outer join statement in Oracle?
  7. ORA – 00933 confusion with inner join and “as”
  8. Oracle (Old?) Joins – A tool/script for conversion?
  9. SQL SELECT from multiple tables
  10. Receive an unexpected symbol error in my PLSQL query [closed]
  11. SQL Query to concatenate column values from multiple rows in Oracle
  12. Select First Row of Every Group in sql [duplicate]
  13. Comparing Dates in Oracle SQL
  14. Oracle Differences between NVL and Coalesce
  15. SQL – How to store and navigate hierarchies?
  16. GROUP BY / aggregate function confusion in SQL
  17. Oracle SQL, concatenate multiple columns + add text
  18. Why does this SQL code give error 1066 (Not unique table/alias: ‘user’)?
  19. Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
  20. SQL Error: ORA-01861: literal does not match format string 01861
  21. Determine Oracle null == null
  22. How to anticipate and escape single quote ‘ in oracle
  23. Oracle insert if row does not exist
  24. SELECT INTO using Oracle
  25. `show create table` equivalent in oracle sql
  26. Query json dictionary data in SQL
  27. ORA-00972 identifier is too long alias column name
  28. ORA-01861: literal does not match format string
  29. Backup SQL Schema Only?
  30. IN vs OR of Oracle, which faster?

Leave a Comment