Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2013
    Posts
    5

    Unanswered: Please help me to understand the query

    SELECT department_name,last_name,project_no
    FROM departments dpt
    JOIN employees emp
    ON dpt.department_no = emp.department_no
    LEFT JOIN projects prj
    ON emp.employee_id = prj.employee_id
    ORDER BY department_name


    ///////////////////////////////////////////////////////////////

    as far as i understood, it will make the inner join first and keep the result in a temp table(lets say X). Then it will do the left join with emp & prj tables & the result will b kept in a temp table(lets say Y). Then it will match X & Y.

    Am i being wrong anywhere? If i am right than what will be the matching thing do? inner join or outer join?
    And if i am wrong than whats the explanation?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    No temp table ever exists
    Code:
    SELECT department_name, 
           last_name, 
           project_no 
    FROM   departments dpt 
           join employees emp 
             ON dpt.department_no = emp.department_no 
           left join projects prj 
                  ON emp.employee_id = prj.employee_id 
    ORDER  BY department_name
    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
    Jul 2013
    Posts
    5
    @anacedent

    By temp i meant temporary tables?
    if not than how does it work?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    memory/RAM data structures
    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.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by shaon View Post
    SELECT department_name,last_name,project_no
    FROM departments dpt
    JOIN employees emp
    ON dpt.department_no = emp.department_no
    LEFT JOIN projects prj
    ON emp.employee_id = prj.employee_id
    ORDER BY department_name


    ///////////////////////////////////////////////////////////////

    as far as i understood, it will make the inner join first and keep the result in a temp table(lets say X). Then it will do the left join with emp & prj tables & the result will b kept in a temp table(lets say Y). Then it will match X & Y.

    Am i being wrong anywhere? If i am right than what will be the matching thing do? inner join or outer join?
    And if i am wrong than whats the explanation?
    It will make the inner join first and keep the result in a temp table(lets say X).
    Then it will do the left join with temp(i.e. X) & prj tables.
    Then sort the resuls to conform with ORDER BY clause(if neccesary), and return the results.

    More operations(like you wrote "match X & Y") are not neccesary.
    The value of emp.employee_id in "ON emp.employee_id = prj.employee_id" is the value in the temp(i.e. X) table.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It will make the inner join first and keep the result in a temp table(lets say X).
    post URL to Oracle documentation that shows above is true.
    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.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It will make the inner join first and keep the result in a temp table(lets say X).
    I'm sorry.

    "keep the result in a temp table" was not accurate. the result might kept in "memory/RAM data structures" as anacedent wrote.

    The point I want to say was "It will make the inner join first, and Then it will do the left join with the result & prj table".
    I didn't care much about the way to keep the result.
    But, it might lead to a misunderstanding(or anger ...).

    Joins
    ...

    Join Conditions

    ...
    ...

    To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns
    and then joins the result to another table based on join conditions containing columns of the joined tables and the new table.
    Oracle continues this process until all tables are joined into the result.
    The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, any available statistics for the tables.
    Note: Bold Ialic were marked by me, not in original document.

Posting Permissions

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