Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Self join to a table

Tags:

sql

self-join

I have a table like

Employee
==================
name      salary
==================
a        10000
b        20000
c        5000
d        40000

i want to get all the employee whose salary is greater than A's salary. I don't want to use any nested or sub query. It has been asked in an interview and hint was to use self join. I really can't figure out how to achieve the same.

like image 224
Mohit Vashistha Avatar asked Jun 14 '10 05:06

Mohit Vashistha


2 Answers

select e1.* from Employee e1, Employee e2  where 
           e2.name = 'a' and
           e1.salary > e2.salary

Using self join

 select e1.* from Employee e1 join Employee e2  on 
           e2.name = 'a' and
           e1.salary > e2.salary
like image 90
Salil Avatar answered Sep 26 '22 05:09

Salil


SELECT emp1.* FROM Employee emp1 JOIN Employee emp2
ON emp2.Name = 'A' and emp1.Salary > emp2.Salary
like image 27
Chinjoo Avatar answered Sep 23 '22 05:09

Chinjoo