topic Re: JOIN statement with Oracle in Operating System - HP-UX

JOIN statement with Oracle

Sylvain CROUET — Tue, 03 Aug 2004 06:09:16 GMT

Hi!

It seems I have problem with my SQL syntax:

SQL> SELECT nn.node_name, no.node_type FROM opc_node_names nn JOIN opc_nodes no ON no.node_id=nn.node_id;
SELECT nn.node_name, no.node_type FROM opc_node_names nn JOIN opc_nodes no ON no.node_id=nn.node_id
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

The star is under the first JOIN.
I use an Oracle 8.1.7 database.

Can someone help me?

Re: JOIN statement with Oracle

Jean-Luc Oudart — Tue, 03 Aug 2004 06:18:08 GMT

I haven't seen this "JOIN" syntax before ??

would that be :
SELECT nn.node_name, no.node_type FROM opc_node_names nn, opc_nodes no
where no.node_id=nn.node_id

Regards,
Jean-Luc

Re: JOIN statement with Oracle

Sylvain CROUET — Tue, 03 Aug 2004 06:37:44 GMT

Yes it should work with the WHERE statement, but I thought that the JOIN statement was a standard one (I used it with SQL Server and MySQL for example).

Re: JOIN statement with Oracle

Jean-Luc Oudart — Tue, 03 Aug 2004 06:38:13 GMT

For more information on join (inner joins, outer joins) check the Oracle documentation on the SQL syntax.
Also :
http://otn.oracle.com/products/oracle9i/daily/jan14.html

Regards,
Jean-Luc

Re: JOIN statement with Oracle

Sanjay Kumar Suri — Tue, 03 Aug 2004 06:43:41 GMT

It seems this will work from Oracle 9i onwards. Sample statement is:

select department_name, city
from department d
JOIN location l
ON (d.location_id = l.id);

select emp_id, city_name, dept_name
from locations l
JOIN departments d ON (d.location_id = l.location_id)
JOIN employees e ON (d.department_id = e.department_id);

sks

Re: JOIN statement with Oracle

Sylvain CROUET — Tue, 03 Aug 2004 06:59:37 GMT

OK, my database version (8.1.7) is too old for this statement.
Thanks.

Re: JOIN statement with Oracle

Jean-Luc Oudart — Tue, 03 Aug 2004 07:55:39 GMT

Sylvain,

found Metalink doc id that says that Oracle is not compliant with ANSI SQL92 regarding this syntax but this supported from Oracle9i

doc id: Note:104306.1

Regards,
Jean-Luc

PS : also found this PDF doc (in French)
http://www.trivadis.ch/Images/nf9isql_teil2_fr_tcm8-3502.pdf

Re: JOIN statement with Oracle

Yogeeraj_1 — Tue, 03 Aug 2004 08:34:37 GMT

hello,

A few notes about the differences: 8i v/s 9i
to join in 8i, just use a where clause:

select *
from emp, dept
where emp.deptno = dept.deptno;

in 9i and up, you could

select ename, dname, emp.deptno, dept.deptno
from SCOTT.EMP inner join SCOTT.DEPT
on emp.deptno = dept.deptno

to outer join 8i, use a (+)

select * from emp, dept where emp.deptno = dept.deptno(+)

is the same as the 9i syntax of:

select ename, dname, emp.deptno, dept.deptno
from SCOTT.EMP LEFT outer join SCOTT.DEPT
on emp.deptno = dept.deptno

whereas

select * from emp, dept where emp.deptno(+) = dept.deptno

is the same as:

select ename, dname, emp.deptno, dept.deptno
from SCOTT.EMP RIGHT outer join SCOTT.DEPT
on emp.deptno = dept.deptno

In 8i, a full outer join would be:

select * from emp, dept where emp.deptno = dept.deptno(+)
UNION ALL
select * from emp, dept where emp.deptno(+) = dept.deptno AND emp.deptno is
null;

versus the 9i syntax (which does the same amount of work -- no magic there)

select *
from SCOTT.EMP FULL outer join SCOTT.DEPT
on emp.deptno = dept.deptno

hope this helps too!
regards
Yogeeraj

Re: JOIN statement with Oracle

Yogeeraj_1 — Tue, 03 Aug 2004 08:34:51 GMT

hello,

A few notes about the differences: 8i v/s 9i
to join in 8i, just use a where clause:

select *
from emp, dept
where emp.deptno = dept.deptno;

in 9i and up, you could

select ename, dname, emp.deptno, dept.deptno
from SCOTT.EMP inner join SCOTT.DEPT
on emp.deptno = dept.deptno

to outer join 8i, use a (+)

select * from emp, dept where emp.deptno = dept.deptno(+)

is the same as the 9i syntax of:

select ename, dname, emp.deptno, dept.deptno
from SCOTT.EMP LEFT outer join SCOTT.DEPT
on emp.deptno = dept.deptno

whereas

select * from emp, dept where emp.deptno(+) = dept.deptno

is the same as:

select ename, dname, emp.deptno, dept.deptno
from SCOTT.EMP RIGHT outer join SCOTT.DEPT
on emp.deptno = dept.deptno

In 8i, a full outer join would be:

select * from emp, dept where emp.deptno = dept.deptno(+)
UNION ALL
select * from emp, dept where emp.deptno(+) = dept.deptno AND emp.deptno is
null;

versus the 9i syntax (which does the same amount of work)

select *
from SCOTT.EMP FULL outer join SCOTT.DEPT
on emp.deptno = dept.deptno

hope this helps too!
regards
Yogeeraj