cancel
Showing results for 
Search instead for 
Did you mean: 

JOIN statement with Oracle

SOLVED
Go to solution
Sylvain CROUET
Respected Contributor

JOIN statement with Oracle

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?
8 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: JOIN statement with Oracle

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
fiat lux
Sylvain CROUET
Respected Contributor

Re: JOIN statement with Oracle

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).
Jean-Luc Oudart
Honored Contributor

Re: JOIN statement with Oracle

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
fiat lux
Sanjay Kumar Suri
Honored Contributor

Re: JOIN statement with Oracle

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Sylvain CROUET
Respected Contributor

Re: JOIN statement with Oracle

OK, my database version (8.1.7) is too old for this statement.
Thanks.
Jean-Luc Oudart
Honored Contributor

Re: JOIN statement with Oracle

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
fiat lux
Yogeeraj_1
Honored Contributor
Solution

Re: JOIN statement with Oracle

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: JOIN statement with Oracle

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)