| Performance in joins [message #404223] |
Thu, 21 May 2009 00:28  |
shahnazurs
Messages: 240
Registered: June 2005
Location: India
|
Senior Member |
|
|
Can you please let me know the difference in the following two queries in performnace?
1)
select e.* from emp e,dept d
where e.deptno=d.deptno
2)
select e.* from emp e
inner join dept d
on(e.deptno=d.deptno)
|
|
|
|
|
|
|
|
| Re: Performance in joins [message #404262 is a reply to message #404260] |
Thu, 21 May 2009 02:36  |
shahnazurs
Messages: 240
Registered: June 2005
Location: India
|
Senior Member |
|
|
I believe 1st one is Oracle Syntax and next one is ANSI one? and also the following statement as well
select * from emp e
where exist (select '1' from dept where deptno=e.deptno)
Kindly let me know amongst these three which one is faster and explanation.
|
|
|
|
| Re: Performance in joins [message #404266 is a reply to message #404262] |
Thu, 21 May 2009 02:48 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
None at all (I can keep doing this all day if you like)
Recent releases of Oracle are able to unnest subqueries and execute them as joins. Assuming DEPT.DEPTNO is unique, the semi-join that is executed will be functionally identical to a regular inner-join.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
