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.

5 Comments

  1. 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.

  2. 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