I like Oracle, you like ANSI – but that’s OK! We can both win!

How to switch back-and-forth.

A wee secret…even ANSI joins often run as Oracle

If you look at the code, UNDER the code, the Optimizer has re-written our statement.

SQL Text Expansion shows us the actual SQL Oracle will run for any given statement…see anything familiar?

Yes, that’s a JOIN in the WHERE clause 🙂

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

9 Comments

  1. Hello Jeff,

    we need to install the SQL Developer on a remote support server. All supporters shall be able to acces the same installation incl. same settings. (Locally I start SQL Developer by clicking on the .exe without any “normal” installation)
    Can we install the SQL Developer as a regular registry registered Software with same settinge for all users? How to do that?

    Thanks
    HoB

    • thatjeffsmith

      Are you REALLY sure you’re OK with everyone being able to see everyone else’s connections and SQL History?

      For now, it’s all stored per OS user – so if you have a shared account on a machine, then you might be OK, as-is.

  2. Dave Campbell Reply

    Hi Jeff

    Code comments seem to break this, for example, refactoring this simple “Oracle” style code..:
    SELECT
    ename,
    dept.deptno,
    dname — my comment
    FROM
    emp,
    dept
    WHERE
    emp.deptno (+) = dept.deptno
    AND emp.deptno >= 30
    ORDER BY
    deptno

    ..to ANSI stops at the FROM :

    SELECT
    ename,
    dept.deptno,
    dname
    FROM

    not a major thing, but it ‘s maybe worth a look. You can undo and get your original code back – trying to refactor back to your original doesn’t work (as it only has a partial code block)

    thanks!

    Dave

    • thatjeffsmith

      Comments aren’t supported at all…this wizardry comes from the query builder where there is no concept of comments.

  3. Nice !

    but some cumbersome syntaxes translate wrong

    SELECT
    *
    FROM
    emp right
    JOIN dept ON emp.deptno = dept.deptno and emp.deptno>30;

    should also use (+) on the second condition to deliver rows where dept.deptno=10 or 20

    • ansi:

      SQL> SELECT
      2 ename, dept.deptno, dname
      3 FROM
      4 emp right
      5 JOIN dept ON emp.deptno = dept.deptno and emp.deptno>=30
      6 ORDER BY DEPTNO
      7 ;

      ENAME DEPTNO DNAME
      ———- ———- ————–
      10 ACCOUNTING
      20 RESEARCH
      ALLEN 30 SALES
      WARD 30 SALES
      JAMES 30 SALES
      BLAKE 30 SALES
      TURNER 30 SALES
      MARTIN 30 SALES
      40 OPERATIONS

      9 rows selected.

      incorrect:
      SQL> SELECT
      2 emp.ename,
      3 dept.deptno,
      4 dept.dname
      5 FROM
      6 emp,
      7 dept
      8 WHERE
      9 emp.deptno (+) = dept.deptno
      10 AND emp.deptno >= 30
      11 ORDER BY
      12 dept.deptno
      13 ;

      ENAME DEPTNO DNAME
      ———- ———- ————–
      ALLEN 30 SALES
      WARD 30 SALES
      MARTIN 30 SALES
      BLAKE 30 SALES
      TURNER 30 SALES
      JAMES 30 SALES

      6 rows selected.

      correct 8i syntax
      SQL> SELECT
      2 emp.ename,
      3 dept.deptno,
      4 dept.dname
      5 FROM
      6 emp,
      7 dept
      8 WHERE
      9 emp.deptno (+) = dept.deptno
      10 AND emp.deptno (+) >= 30
      11 ORDER BY
      12 dept.deptno
      13 ;

      ENAME DEPTNO DNAME
      ———- ———- ————–
      10 ACCOUNTING
      20 RESEARCH
      ALLEN 30 SALES
      WARD 30 SALES
      JAMES 30 SALES
      BLAKE 30 SALES
      TURNER 30 SALES
      MARTIN 30 SALES
      40 OPERATIONS

      9 rows selected.

  4. nice, but it doesn’t like the USING keyword, can’t handle that. Perhaps in the next release?

    • drop table t1 purge;
      drop table t2 purge;

      create table t1 (t1_id number);
      create table t2 (t2_id number, t1_id number);

      select *
      from t1
      join t2
      using (t1_id);

      — now try and toggle ansi/oracle join, get this text in a popup

      Text contains unsupported syntax.
      Parsing error:
      Failed to parse SQL query.
      Invalid SELECT statement.
      Unexpected token “using” at line 1, pos 36.
      Query Builder disabled.
      —————————————-
      select * from t1 join t2 using (t1_id);
      —————————————-

Write A Comment