This discussion is archived
2 Replies Latest reply: Feb 10, 2013 1:33 AM by Frank Kulash RSS

joins

pawii Newbie
Currently Being Moderated
what is this
is it a self join???

select e.hire_date,s.last_name,s.hire_date
from employees e,employees s
where e.last_name='Davies'and s.hire_date>e.hire_date
  • 1. Re: joins
    jeneesh Guru
    Currently Being Moderated
    987018 wrote:
    what is this
    is it a self join???

    select e.hire_date,s.last_name,s.hire_date
    from employees e,employees s
    where e.last_name='Davies'and s.hire_date>e.hire_date
    This is some kind of Self Non-Equi join...

    The query will give all the employees, whose hire_date is greater than that of 'Davies'.

    This will give you proper output if the table has only one row with the last name you have provided.

    And to add, for this particular query, SELF JOIN will not be required, as you can achieve the same thing using Analytics..
    (Assuming Last name is unique..)
    select last_name,hire_date,davies_hire_date
    from(
          select last_name,hire_date,
                 max(decode(last_name,'Davies',hire_date)) 
                    over() davies_hire_date
          from employees 
        )
    where hire_date > davies_hire_date;
    Edited by: jeneesh on Feb 10, 2013 3:28 PM
  • 2. Re: joins
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    987018 wrote:
    what is this
    is it a self join???

    select e.hire_date,s.last_name,s.hire_date
    from employees e,employees s
    where e.last_name='Davies'and s.hire_date>e.hire_date
    Yes, it's a self-join. It's a join between tables e and s, where e and s are the same table; that's exactly what a self-join is.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points