ANSI or Oracle Style Joins?

9 Comments

1. 

   HoB 5 years ago Reply

   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 Post Author 5 years ago

     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 5 years ago 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 Post Author 5 years ago

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

3. 

   Laurent Schneider 5 years ago Reply

   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

   • 

     Laurent Schneider 5 years ago

     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. 

   Ian 5 years ago Reply

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

   • 

     thatjeffsmith Post Author 5 years ago

     Example, or no chance.

   • 

     Ian 5 years ago

     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);
     —————————————-