SQL Self Join Concept | Most Asked Interview Question | Employee Salary More than Manager's Salary

one more solution:
with cte as(select A.* , B.Salary as manger_salary from emp_manager as A, emp_manager B where A.manager_id=B.emp_id)
select emp_id, emp_name from cte where salary> manger_salary

I had been searching for self join videos. But I did not understand it's working then I came across ur video and you explained in such a easy way that any layman can understand. Thank u so much. Keep doing such great work.

Thanks for so clear explanation. Understood

please increase ur voice ..just now i subscribed

nicely explained!

lovely explaining

grt work sir

1

Your video is very helpful, sir. The way you explain complex concepts is very understandable

please upload data set as well or provide drive link whenever u upload practical

Hi.. i have employee table in that we have ename and sal columns. From employee table we need employee name who is getting max sal without using analytical,order by and aggregate functions. How can we do any one help on this

when I run the query say table does not exit

I have solved this question same way in leetcode

great, thanks for the explanation

3/122

It became very easy to understand the concept the way you show joining manager id with the second table emp id..... Thank you so much for the clear understanding

Thanks for this Ankit. It was really helpful for me in understanding the concept of self joins. Really appreciated!

create table emp_manager(emp_id int,emp_name varchar(50),salary int,manager_id int);
insert into emp_manager values( 1 ,'Ankit', 10000 ,4 );
insert into emp_manager values( 2 ,'Mohit', 15000 ,5 );
insert into emp_manager values( 3 ,'Vikas', 10000 ,4 );
insert into emp_manager values( 4 ,'Rohit', 5000 ,2 );
insert into emp_manager values( 5 ,'Mudit', 12000 ,6 );
insert into emp_manager values( 6 ,'Agam', 12000 ,2 );
insert into emp_manager values( 7 ,'Sanjay', 9000 ,2 );
insert into emp_manager values( 8 ,'Ashish', 5000 ,2 );

If we just use join also it works select e.*,m.salary as sal from emp_manager as e join emp_manager as m on e.manager_id = m.emp_id where e.salary > m.salary this my query and it worked

SELF JOIN concept explained in the best way. thanks a lot.
Could you please explain INDEX and CLUSTER INDEX and NON-CLUSTER INDEX concepts please?

Thank you for these amazing videos its helping me to learn the sql in simplest way.

Solution Alert

select emp_id,emp_name,emp_salary,manager_id, manager_salary from(
select a.emp_id,a.emp_name , a.salary as emp_salary,a.manager_id ,b.salary as manager_salary from emp_manager a join emp_manager b on a.manager_id =b.emp_id ) where emp_salary > manager_salary

the most lucid explanation on whole utube without any complication.. amazing bro !!

@ankitbansal6 :I thought self join will have where clause and condition and no need of join cause. Can you plz clarify whether self join and inner join are one and the same as the syntax of both is different and whether can it be used interchangeably

Using self join for Emp Salary >Manager Salary:

select
e1.emp_id as Emp_id,
e1.emp_name as employee_name,
e1.salary as emp_salary,
m1.emp_id as Manager_id,
m1.emp_name as manager_name,
m1.salary as manager_salary
from
emp3 e1,emp3 m1
where
m1.emp_id=e1.manager_id
and e1.salary>m1.salary
order by emp_id;

Hi Ankit,
Kindly post the Table schema queries in the description for reference and solution query too..

Beautifully explained

DAMN ! the two table creation on excel was soo good way of explaining ! you earned a subscriber ! 🤩

Thank you sir..

Very helpful concept 👍🏻

IT'S ALWAYS THE UNDERRATED VID THAT'S LEGIT! THANK YOU! <3

god bless u xdd

thank you so much dude you're a god

scripts:

create table emp_manager(emp_id int,emp_name varchar(20),salary int,manager_id int);
insert into emp_manager values(1,'Ankit',10000,4);
insert into emp_manager values(2,'Mohit',15000,5);
insert into emp_manager values(3,'Vikas',10000,4);
insert into emp_manager values(4,'Rohit',5000,2);
insert into emp_manager values(5,'Mudit',12000,6);
insert into emp_manager values(6,'Agam',12000,2);
insert into emp_manager values(7,'Sanjay',9000,2);
insert into emp_manager values(8,'Ashish',5000,2);
select * from emp_manager;

with cte1 as(
select e.emp_id,e.emp_name,m.emp_name as manager_name,e.salary as emp_salary,m.salary as manager_salary
from emp_manager e join emp_manager m on e.manager_id=m.emp_id
)
select * from cte1 where emp_salary>manager_salary

Thanks for this example !
what would be the solution to find out those employees who have highest salary under each manager.

Tables:
Employee:
Employee_Id name salary
1 Ram 4500
2 Gopi 17500
3 shyam 9500
4 Nisha 13500

Salary_range:
From_sal To_sal Grade
0 5000 A
5001 10000 B
10001 15000 C
15001 20000 D

Output:
Name Grade
Ram A
Gopi D
shyam B
Nisha C


I need a query to get the output result.

If any employee is not having any manager , I mean NULL. Then how to write that? I want NULL in the columns manager_name and manager_salary.

Thanks for making it so easy

select e.emp_id, e.emp_name, e.salary as emp_salary, f.emp_name as manager_nm, f.salary as manager_sal from emp_manager e
inner join emp_manager f
on e.manager_id = f.emp_id
where e.salary > f.salary
order by f.emp_name;

Thanks Ankit, Great Content❣

Using Excel and pasting the manager info next to employee info was a great way of explaining! Thank you. You make the life of a Data person so easy!

Sir, if we inner join emp id of emp with manager id of manager table, will it work same?

Awesome Ankit got your channel from Linkeidin and its best thing happened.

Well Ankit's salary is greater than his manager's salary at Amazon 😀. Nice tutorial btw. Great work.

can I write :-
Table name - emp_manager

select e.empid, e.salary, m.manager_id, m.salary from emp_manager as e, emp_manager as m
where e.salary > m.salary; pls sir tell me .

create table emp_manager(emp_id int,emp_name varchar(50),salary int(20),manager_id int(10));
insert into emp_manager values( 1 ,'Ankit', 10000 ,4 );
insert into emp_manager values( 2 ,'Mohit', 15000 ,5 );
insert into emp_manager values( 3 ,'Vikas', 10000 ,4 );
insert into emp_manager values( 4 ,'Rohit', 5000 ,2 );
insert into emp_manager values( 5 ,'Mudit', 12000 ,6 );
insert into emp_manager values( 6 ,'Agam', 12000 ,2 );
insert into emp_manager values( 7 ,'Sanjay', 9000 ,2 );
insert into emp_manager values( 8 ,'Ashish', 5000 ,2 );

Very nice explanation

Please share the script to make the table.

Hi Ankit
Could you make video on performance tunning topic.

Hey Ankit, if we write query as " on e.emp_id = m.mgr_id" and rest query remains same will it yield same result?

Hi Ankit,
Can you please make videos on Python and ML please would be really helpful.
Thanks