Oracle (Old?) Joins – A tool/script for conversion?

by

The (+) is Oracle specific pre-ANSI-92 OUTER JOIN syntax, because ANSI-89 syntax doesn’t provide syntax for OUTER JOIN support.

Whether it is RIGHT or LEFT is determined by which table & column reference the notation is attached to. If it is specified next to a column associated with the first table in the FROM clause – it’s a RIGHT join. Otherwise, it’s a LEFT join. This a good reference for anyone needing to know the difference between JOINs.

First query re-written using ANSI-92 syntax:

    SELECT e.lastname,
           d.department_name
      FROM EMPLOYEES e
RIGHT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid

Second query re-written using ANSI-92 syntax:

   SELECT e.lastname,
          d.department_name
     FROM EMPLOYEES e
LEFT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid

More Related Contents:

  1. SQL recursive query on self referencing table (Oracle)
  2. create table with sequence.nextval in oracle [duplicate]
  3. Receive an unexpected symbol error in my PLSQL query [closed]
  4. PL SQL UPDATE STATEMENT
  5. Splitting string into multiple rows in Oracle
  6. Search All Fields In All Tables For A Specific Value (Oracle)
  7. How do I reset a sequence in Oracle?
  8. ORA-00904: invalid identifier
  9. What is the string concatenation operator in Oracle?
  10. Is it possible to output a SELECT statement from a PL/SQL block?
  11. Oracle “(+)” Operator
  12. How can I get column names from a table in Oracle?
  13. Is there any boolean type in Oracle databases?
  14. passing table and column name dynamically using bind variables
  15. Is there any way to flush output from PL/SQL in Oracle?
  16. Oracle – How to create a materialized view with FAST REFRESH and JOINS
  17. Deferrable Constraints in SQL Server
  18. PLSQL Insert into with subquery and returning clause
  19. What does the colon sign “:” do in a SQL query?
  20. How to populate calendar table in Oracle?
  21. What can cause an Oracle ROWID to change?
  22. Does ‘Select’ always order by primary key?
  23. Advice Using Pivot Table in Oracle
  24. Oracle- Split string comma delimited (string contains spaces and consecutive commas)
  25. Local Temporary table in Oracle 10 (for the scope of Stored Procedure)
  26. How do NULL values affect performance in a database search?
  27. Subtracting Dates in Oracle – Number or Interval Datatype?
  28. Query a Table’s Foreign Key relationships
  29. ORACLE After update trigger: solving ORA-04091 mutating table error
  30. Using bind variables with dynamic SELECT INTO clause in PL/SQL

Leave a Comment