2 Replies Latest reply: Feb 10, 2013 3:33 AM by Frank Kulash RSS

    joins

    pawii
      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
          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
            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.