Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    5

    Unanswered: Using predicate in joins....

    what is difference between the below mentioned queries? why its giving diff ans?


    Query 1:

    select a.col1, b.col1
    from table1 a, table2 b
    where a.id = b.id and col2 = '123'


    select a.col1, b.col1
    from table1 a inner join table2 b
    on a.id = b.id and col2 = '123'

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what is difference between the below mentioned queries? why its giving diff ans?
    post CREATE TABLE & INSERT statements so we can reproduce what you report.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2011
    Posts
    5
    In my post instead of left outer join i put inner join... Sorry for that...
    For example:

    Query 1:
    select count(*)
    from hr.employees e left outer join hr.departments d
    on (e.department_id = d.department_id and e.employee_id = 100)

    Query 2:
    select count(*)
    from hr.employees e left outer join hr.departments d
    on (e.department_id = d.department_id)
    where e.employee_id = 100

    Query 3:
    select count(*)
    from hr.employees e, hr.departments d
    where e.department_id = d.department_id(+)
    and e.employee_id = 100


    After seeing these example am able to understand what i missed....
    In Query 2 the join wil be applied and then from the result set its picking the employee_id = 100 and displaying.. Am i rit??
    Can u tell me the order of execution for the Query 3??
    which one wil be executed first in the where clause... Join condition or employee_id = 100...???

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Mahalakshmi S View Post
    Can u tell me the order of execution for the Query 3??
    which one wil be executed first in the where clause... Join condition or employee_id = 100...???
    In declarative language (where SQL belongs) it makes no sense to talk about "order of execution". It is not part of the language, which only states the result you want to achieve. The execution lies on its interpreter (database), who decides how shall the statement be processed.

    Oracle provides explain plan which describes steps which were taken to achieve the required result set. Just study it.

  5. #5
    Join Date
    Jun 2011
    Posts
    5

    In reply...

    Thanks... I will study the links...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •