Home » SQL & PL/SQL » SQL & PL/SQL » ANSI Joins
ANSI Joins [message #206976] Sat, 02 December 2006 22:27 Go to next message
Messages: 10
Registered: December 2006
Junior Member
Hai everyone,

I am new to Oracle and really need some guidance to figure out certain things.

I have a doubt on joins.

select e.empno, e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno
and substr(e.ename,1,1) = 'S';

How can I rewrite it in ANSI compliant equivalent statement usable on
the Oracle database?

select empno, ename, loc
from emp join dept
on emp.deptno = dept.deptno
where substr(emp.ename,1,1) = 'S';


select empno, ename, loc
from emp join dept
on emp.deptno = dept.deptno
and substr(emp.ename,1,1) = 'S';

Which one of them is right, using where or and for the last condition?

Do guide me.


Re: ANSI Joins [message #207022 is a reply to message #206976] Sun, 03 December 2006 22:58 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
For inner joins - which is what you are doing - it makes no difference, although it is considered proper to place JOIN conditions in the JOIN clause, and filter conditions in the WHERE clause.

Outer joins are a different story. Try both combinations with an outer join and see what happens.

Ross Leishman
Previous Topic: Running sql script inside another sql script
Next Topic: to get values between date
Goto Forum:

Current Time: Thu Jul 20 11:24:42 CDT 2017

Total time taken to generate the page: 0.41397 seconds