SQL Self Join Concept | Most Asked Interview Question | Employee Salary More than Manager's Salary
ฝัง
- เผยแพร่เมื่อ 11 ม.ค. 2022
- In this video we will understand self join concept. Why we need it and how to do it. we will find solution for most asked interview question:
Find employees with salary more than their mangers salary
![แข่งแลกของ 1 วัน!! ของใครแพงสุดชนะ!! [Ver.2024]](http://i.ytimg.com/vi/joRT59ilPHg/mqdefault.jpg)
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!
😊
Correct thanks Ankit 🙌🙌... explaining same without excel was like tutor teaching to only class toppers, just imagine one who can imagine without excel can definitely solve this interview question 😅 it was just putting into syntax task for that kind of guy.
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 );
Saviour
thnx
int(20) ? will it work ? i think size is not necessary
@@alokgupta35 yes
thank u
great way of explaining. kudos!
Untill before watching this video I am just joining the tables withing itself and getting the empty table as result lol.
Thank you Ankit bro for explaining clearly and simply.
IT'S ALWAYS THE UNDERRATED VID THAT'S LEGIT! THANK YOU!
Hi Ankit,
Kindly post the Table schema queries in the description for reference and solution query too..
Thanks for making it so easy
DAMN ! the two table creation on excel was soo good way of explaining ! you earned a subscriber ! 🤩
Thank you 😊
Thanks Ankit, Great Content❣
Thank you so much
Superb!!!!
thank you so much dude you're a god
You make SQL look so easy, really good. Thanks mate
🙏
Thanks
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
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.
It's my pleasure😊
as usual, great content. You have been a guru to us. Thanks for your time. It is a selfless act. Thanks again
My pleasure!
I have solved this question same way in leetcode
Awesome Ankit got your channel from Linkeidin and its best thing happened.
Completed ❤
Great Way to explain with Excel not many would explain in Detail like this !
Glad you liked it!
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
You are most welcome
Thanks for this Ankit. It was really helpful for me in understanding the concept of self joins. Really appreciated!
Glad to hear that
Clear explanation! thank u!
Glad you liked it 😊
Thanks for so clear explanation. Understood
Great to hear!
great, thanks for the explanation
You are welcome!
Thank you for these amazing videos its helping me to learn the sql in simplest way.
Great to hear!
Thank you sir.
For the explanation.very clear
You are welcome
please upload data set as well or provide drive link whenever u upload practical
very crystal clear and detailed explanation
Glad you liked it😊
Well explained, thank you so much. Subscribed !
Awesome, thank you!
Hi Ankit,
Can you please make videos on Python and ML please would be really helpful.
Thanks
Hi Ankit
Thank you 🙂
If possible please share the create table and insert statements for every video
Oracle
create table emp_manager(
emp_id number,
emp_name varchar2(50),
salary number,
manager_id number
);
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 );
Your video is very helpful, sir. The way you explain complex concepts is very understandable
Great 😊
grt work sir
Thanks a lot
Very helpful concept 👍🏻
Glad it was helpful!
lovely explaining
Thank you! 🙂
Beautifully explained
Thank you so much 🙂
Great explanation 👍
Thank you 😊
the most lucid explanation on whole utube without any complication.. amazing bro !!
Glad you liked it!
Done
Very nice explanation
Thank you
Very well explained brother.
Glad it was helpful!
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?
Noted
Very nicely explained
Keep watching🙌
Well Ankit's salary is greater than his manager's salary at Amazon 😀. Nice tutorial btw. Great work.
Haha 😃
Thank you sir..
Most welcome
Can we combine different columns using inner join
nicely explained!
Glad you liked it
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
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 );
Thanks for this example !
what would be the solution to find out those employees who have highest salary under each manager.
Rank partition by manager and filter
Hello
Why is manager id taken from the employee table
And not manager table?
Thanks
god bless u xdd
Hi Ankit, thanks for the clear explanation. Small question on the JOIN part. Any particular reason for using INNER JOIN there? LEFT JOIN would also work and produce similar result there right?
Yes but there is no need of left join.When inner join works then why to use left join.
INNER JOIN is optimized than OUTER JOIN
@@ankitbansal6 I realized one thing how Emp_id = 1 is getting in output even though 1 is not present in manager_Id ? I am trying to match output but not matching .
3/122
Hello Sir, You mentioned self join but in the answer you used Inner Join
Hi, i have a slightly different question to ask. How can i put a check constraint on the manager_id column such that it shouldn't have any other values except for the ones present in emp_id in postgresql
We can put a forein key constraint
@@ankitbansal6 thanks dude. I wasn't aware that we can add a foreign key constraint to the same table.
@@arbazadam3407 we can 😊
Hi Ankit, Correct me if I'm wrong, Don't you think that 'emp_id: 1, emp_name: Ankit' should not come in the result as you have used inner join on the emp_id = manager_id.
But Ankit's manager id is present in table in emp I'd column.
when I run the query say table does not exit
please explain the reason for the join connection based on a.manager_id = b.id? why we cant use a.id = b.manager_id?
You can do that also. Accordingly you will have to use alias for manager name and emp name and salary.
@@ankitbansal6 thanks
Hi Ankit
Could you make video on performance tunning topic.
Sure
hello ankit what if one of the manager id is null and how it will join in that case to get emp salary>mgr salary
If an employee does not have a manager then you cannot compare.
Can you please give more real time scenarios where Self Join is used?
Since it's 4 days and nobody replied to your comment, I would recommend you to ask the same question to ChatGPT.
Hey Ankit, if we write query as " on e.emp_id = m.mgr_id" and rest query remains same will it yield same result?
Nope
@@ankitbansal6 can you please explain why the queries are giving different results when we are changing the on condition?
@@Ramesh00300 you need to watch the video again to understand
When specifying a condition in the WHERE clause, it applies after the join has been performed. This means that the join conditions defined in the ON clause have already been applied, and the WHERE clause further filters the resulting data.
In contrast, the ON clause specifies the conditions for joining tables. It dictates how the tables are connected, such as matching columns or other relationships. This condition is used during the join itself, influencing which rows from both tables are combined.
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 .
please increase ur voice ..just now i subscribed
Sure
Sir, if we inner join emp id of emp with manager id of manager table, will it work same?
No.
@@ankitbansal6 how to imagine sir which column to take on left table and which one on right
@@moyeenshaikh9915 watch video again
1
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.
Just do left join
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
could you please share script to create these tables?
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),(2,'Mohit',15000,5),(3,'Vikas',10000,4),(4,'Rohit',5000,2),
(5,'Mudit',12000,6),(6,'Agam',12000,2),(7,'Sanjay',9000,2),(8,'Ashish',5000,2);
select * from emp_manager;
Why did he use INNER JOIN and not SELF JOIN? Please someone tell me!
There is no keyword as self join. When you join a table with itself it's called self join.
@@ankitbansal6 can't we do this question by joining this employee table with itself?
@@vanshgrover_19 that's what we have done
Please share the script to make the table.
Nvm, I created the script:
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),(2,'Mohit',15000,5),(3,'Vikas',10000,4),(4,'Rohit',5000,2),
(5,'Mudit',12000,6),(6,'Agam',12000,2),(7,'Sanjay',9000,2),(8,'Ashish',5000,2);
select * from emp_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.
Just join with condition salary between from sal and to salary
Below Query will give the desired result:
Select E.emp_name as 'Name',S.Grade from EmployeeT E
Inner join Salary_range S
on E.salary between S.From_Sal and S.To_Sal
Here is the table scripts for the same:
create table EmployeeT(
emp_id int,
emp_name varchar(20),
salary int,)
insert into EmployeeT
values (1, 'Ram', 4500);
insert into EmployeeT
values (3, 'Gopi', 17500);
insert into EmployeeT
values (3, 'Shyam', 9500);
insert into EmployeeT
values (4, 'Nisha', 13500);
create table Salary_range(
From_sal int,
To_sal int,
Grade varchar(20),
)
insert into Salary_range
values (0,5000,'A');
insert into Salary_range
values (5001,10000,'B');
insert into Salary_range
values (10001,15000,'C');
insert into Salary_range
values (15001,20000,'D');
I solved a similar problem on Leetcode today!
Used sub queries for it!
Cool
This was a really good explanation!
I’ll definitely be following your videos! :D
can't we use this condition on e.emp_id=m.manager_id ?
Using this giving different output
Thanks Ankit ! I understood this but under self join ,
I came across a different question from sqlzoo:
stops:stops(id, name)
route(num, company, pos, stop)
Find the routes involving two buses that can go from Craiglockhart to Lochend.
Show the bus no. and company for the first bus, the name of the stop for the transfer,
and the bus no. and company for the second bus.
I tried to find the solution but getting some error:
Could you please take this type of example .
I also saw some solutions but coudn't understand it .like below is an working soution for above question but I didn't understood on what basis they are joining on company and num fields ?
Sol:
SELECT a.num, a.company, stops.name, d.num, d.company
FROM route a JOIN route b ON a.company = b.company AND a.num = b.num
JOIN stops ON b.stop = stops.id
JOIN route c ON c.stop = stops.id
JOIN route d ON c.company = d.company AND c.num = d.num
WHERE a.stop = (SELECT id FROM stops WHERE name = 'Craiglockhart')
AND d.stop = (SELECT id FROM stops WHERE name = 'Lochend')
ORDER BY a.num, stops.name, d.num
It woud be helpful if you can help me to understand on what basis they are joining on company and num fields ?
Sure I will check that question.
@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;
It's same thing
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
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
To find employees whose salary is more than manager we could have filtered during JOIN only while declaring ON condition as:
FROM emp e JOIN emp m
ON e.manager_id = m.emp_id
AND e.salary>m.salary;
In case of inner join both are same 😊