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
Пікірлер: 173
@spd31552 жыл бұрын
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 );
@satyanathparvatham44062 жыл бұрын
Saviour
@devashishduklan81542 жыл бұрын
thnx
@alokgupta35 Жыл бұрын
int(20) ? will it work ? i think size is not necessary
@rishabhkesarwani5761 Жыл бұрын
@@alokgupta35 yes
@NaveenKumar-fq4sb Жыл бұрын
thank u
@amrutdeshpande052 жыл бұрын
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!
@ankitbansal62 жыл бұрын
😊
@dhananjaymali6242 Жыл бұрын
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.
@ajinkyaadhotre5336 Жыл бұрын
DAMN ! the two table creation on excel was soo good way of explaining ! you earned a subscriber ! 🤩
@ankitbansal6 Жыл бұрын
Thank you 😊
@kirankapruwan8892 Жыл бұрын
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.
@ankitbansal6 Жыл бұрын
It's my pleasure😊
@vinipowerbi Жыл бұрын
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
@ankitbansal6 Жыл бұрын
You are most welcome
@spokenenglishworld16509 күн бұрын
Thank you so much ankit 100 times...I was not able to understand self join but your video made it possible and i have been searching this kinda content since a long time..thank you so much once again...i dont usually comment but it is worth to do it today☺
@ankitbansal69 күн бұрын
Thank you 😊
@ayoushisingh563125 күн бұрын
Great way of explaination by putting the same table next to the original table. Thanks!!
@sanam63232 ай бұрын
Great. Ager long years I am in the correct KZfaq channel for SQL. Thanks Ankit.
@ankitbansal62 ай бұрын
Keep going 💪
@Venom-yk3wu Жыл бұрын
the most lucid explanation on whole utube without any complication.. amazing bro !!
@ankitbansal6 Жыл бұрын
Glad you liked it!
@sridharmurari300710 ай бұрын
Great Way to explain with Excel not many would explain in Detail like this !
@ankitbansal610 ай бұрын
Glad you liked it!
@Nikhilg-rs7iv5 ай бұрын
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.
@sanjanashuklaa203129 күн бұрын
video was crystal clear😍😍 i don't know how effortlessly i understood this concept because it was you who was explaining thankyou plz make more video on other concepts
@ankitbansal629 күн бұрын
Glad to know 🙂
@naveennvnkumar46152 жыл бұрын
You make SQL look so easy, really good. Thanks mate
@ankitbansal62 жыл бұрын
🙏
@shuaibsaqib5085 Жыл бұрын
Hi Ankit, Kindly post the Table schema queries in the description for reference and solution query too..
@user-iq8fe5nn8f28 күн бұрын
Amazing Amazing seriously Amazing .... 👏 👏 👏 Thank you so mych
@vinitpatidar5617Ай бұрын
Most simple explanation. Thanks
@dfkgjdflkg6 ай бұрын
as usual, great content. You have been a guru to us. Thanks for your time. It is a selfless act. Thanks again
@ankitbansal66 ай бұрын
My pleasure!
@sairajesh6895 Жыл бұрын
IT'S ALWAYS THE UNDERRATED VID THAT'S LEGIT! THANK YOU!
@dudechany2 жыл бұрын
Well Ankit's salary is greater than his manager's salary at Amazon 😀. Nice tutorial btw. Great work.
@ankitbansal62 жыл бұрын
Haha 😃
@hemantsah85675 күн бұрын
That's how manager have grudges against some employees, they use Ankit Bansal's query. :)
@prithak32636 ай бұрын
great way of explaining. kudos!
@shubhamsri4561 Жыл бұрын
Your video is very helpful, sir. The way you explain complex concepts is very understandable
@ankitbansal6 Жыл бұрын
Great 😊
@RohithS-ig4hl Жыл бұрын
Thanks for this Ankit. It was really helpful for me in understanding the concept of self joins. Really appreciated!
@ankitbansal6 Жыл бұрын
Glad to hear that
@rahulranjan67408 ай бұрын
very crystal clear and detailed explanation
@ankitbansal68 ай бұрын
Glad you liked it😊
@aniketraut6864 Жыл бұрын
Thank you for these amazing videos its helping me to learn the sql in simplest way.
@ankitbansal6 Жыл бұрын
Great to hear!
@sumanacharya461 Жыл бұрын
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
@milindzuge9062 жыл бұрын
Thanks Ankit, Great Content❣
@pspreetesh41662 ай бұрын
sir why did you give inner join instead of self join? inner join only gives common records b/w 2 tables and self join used for 1 table by itself. Please answer
@enjoyfriends8735 ай бұрын
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 );
@anchitgupta4772 Жыл бұрын
I have solved this question same way in leetcode
@debasmitabanerjee2835Ай бұрын
Best explanation!!!!
@bhaveshbharti4782 Жыл бұрын
Thanks for so clear explanation. Understood
@ankitbansal6 Жыл бұрын
Great to hear!
@Travelling_With_Kamalu8 ай бұрын
Thank you sir. For the explanation.very clear
@ankitbansal68 ай бұрын
You are welcome
@vishnujatav63292 жыл бұрын
Very nice explanation
@ankitbansal62 жыл бұрын
Thank you
@LS8636 Жыл бұрын
Thanks for making it so easy
@surajkurle6962Ай бұрын
thank u very easy
@gauravthukral70422 жыл бұрын
Great explanation 👍
@ankitbansal62 жыл бұрын
Thank you 😊
@praveenkumarrai101 Жыл бұрын
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 );
@hiteshjanuskar7319 Жыл бұрын
Very helpful concept 👍🏻
@ankitbansal6 Жыл бұрын
Glad it was helpful!
@aashishmalhotra2 жыл бұрын
Awesome Ankit got your channel from Linkeidin and its best thing happened.
@mohiuddinansari93566 ай бұрын
Well explained, thank you so much. Subscribed !
@ankitbansal66 ай бұрын
Awesome, thank you!
@lazygeek431 Жыл бұрын
great, thanks for the explanation
@ankitbansal6 Жыл бұрын
You are welcome!
@adityakaushik6417 Жыл бұрын
Thanks for this example ! what would be the solution to find out those employees who have highest salary under each manager.
@ankitbansal6 Жыл бұрын
Rank partition by manager and filter
@mahesh6701 Жыл бұрын
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?
@ankitbansal6 Жыл бұрын
Noted
@techgalaxy1007 ай бұрын
Very well explained brother.
@ankitbansal67 ай бұрын
Glad it was helpful!
@sauravips Жыл бұрын
Beautifully explained
@ankitbansal6 Жыл бұрын
Thank you so much 🙂
@meerasrinivasagopalan96622 жыл бұрын
Clear explanation! thank u!
@ankitbansal62 жыл бұрын
Glad you liked it 😊
@raushansingh7530Ай бұрын
select emp.emp_name as Employee_name from emp_manager as emp inner join emp_manager as manager on emp.manager_id = manager.emp_id where emp.salary > manager.salary Thanks Ankit sr
@sayansengupta820711 ай бұрын
Superb!!!!
@vsagar4b388 ай бұрын
Very nicely explained
@ankitbansal68 ай бұрын
Keep watching🙌
@pavangsk8404 Жыл бұрын
lovely explaining
@ankitbansal6 Жыл бұрын
Thank you! 🙂
@explorer_baba27502 жыл бұрын
please explain the reason for the join connection based on a.manager_id = b.id? why we cant use a.id = b.manager_id?
@ankitbansal62 жыл бұрын
You can do that also. Accordingly you will have to use alias for manager name and emp name and salary.
@explorer_baba27502 жыл бұрын
@@ankitbansal6 thanks
@mohitmalviya1928 Жыл бұрын
grt work sir
@ankitbansal6 Жыл бұрын
Thanks a lot
@ashish-blessings8 ай бұрын
Thank you so much
@vishalsonawane.89054 ай бұрын
Done
@nishantbodke Жыл бұрын
thank you so much dude you're a god
@ganeshtaware98832 жыл бұрын
Thanks
@vutv57428 ай бұрын
Completed ❤
@pavan52082 жыл бұрын
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?
@ankitbansal62 жыл бұрын
Yes but there is no need of left join.When inner join works then why to use left join.
@radhikajujjavarapu69712 жыл бұрын
INNER JOIN is optimized than OUTER JOIN
@uttamthakur201610 ай бұрын
@@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 .
@muazmalik601 Жыл бұрын
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
@arpitmishra1783 Жыл бұрын
nicely explained!
@ankitbansal6 Жыл бұрын
Glad you liked it
@Vedantsinghvi16117 ай бұрын
Hello Why is manager id taken from the employee table And not manager table? Thanks
@user-bd8li1xz1x5 ай бұрын
Can we combine different columns using inner join
@PriyankaPatil-nj2qb Жыл бұрын
Thank you sir..
@ankitbansal6 Жыл бұрын
Most welcome
@sass5204 ай бұрын
Hello Sir, You mentioned self join but in the answer you used Inner Join
@thedefende-i7g Жыл бұрын
please upload data set as well or provide drive link whenever u upload practical
@satyamtyagi30762 жыл бұрын
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.
@ankitbansal62 жыл бұрын
But Ankit's manager id is present in table in emp I'd column.
@neelshah88032 жыл бұрын
Hi Ankit, Can you please make videos on Python and ML please would be really helpful. Thanks
@rajivjain61652 жыл бұрын
Hi Ankit Could you make video on performance tunning topic.
@ankitbansal62 жыл бұрын
Sure
@niveditasingh11762 жыл бұрын
Hey Ankit, if we write query as " on e.emp_id = m.mgr_id" and rest query remains same will it yield same result?
@ankitbansal62 жыл бұрын
Nope
@Ramesh003002 жыл бұрын
@@ankitbansal6 can you please explain why the queries are giving different results when we are changing the on condition?
@ankitbansal62 жыл бұрын
@@Ramesh00300 you need to watch the video again to understand
@mohitmotwani9256 Жыл бұрын
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.
@hellotohappiness1028 Жыл бұрын
please increase ur voice ..just now i subscribed
@ankitbansal6 Жыл бұрын
Sure
@yatinshekhar787 Жыл бұрын
3/122
@AnilKumar-oe1bb10 ай бұрын
Can you please give more real time scenarios where Self Join is used?
@sanilkumarbarik91519 ай бұрын
Since it's 4 days and nobody replied to your comment, I would recommend you to ask the same question to ChatGPT.
@rajeshgoud96197 ай бұрын
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
@ankitbansal67 ай бұрын
If an employee does not have a manager then you cannot compare.
@divyagouda5285 Жыл бұрын
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
@arbazadam34072 жыл бұрын
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
@ankitbansal62 жыл бұрын
We can put a forein key constraint
@arbazadam34072 жыл бұрын
@@ankitbansal6 thanks dude. I wasn't aware that we can add a foreign key constraint to the same table.
@ankitbansal62 жыл бұрын
@@arbazadam3407 we can 😊
@amritasinha2784 Жыл бұрын
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.
@ankitbansal6 Жыл бұрын
Just join with condition salary between from sal and to salary
@theraizadatalks14 Жыл бұрын
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');
@techtalk9903 Жыл бұрын
god bless u xdd
@umeshroy6898 Жыл бұрын
when I run the query say table does not exit
@amritasinha2784 Жыл бұрын
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.
@ankitbansal6 Жыл бұрын
Just do left join
@siramar24532 жыл бұрын
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 .
@vanshgrover_194 ай бұрын
Why did he use INNER JOIN and not SELF JOIN? Please someone tell me!
@ankitbansal64 ай бұрын
There is no keyword as self join. When you join a table with itself it's called self join.
@vanshgrover_194 ай бұрын
@@ankitbansal6 can't we do this question by joining this employee table with itself?
@ankitbansal64 ай бұрын
@@vanshgrover_19 that's what we have done
@moyeenshaikh99152 жыл бұрын
Sir, if we inner join emp id of emp with manager id of manager table, will it work same?
@ankitbansal62 жыл бұрын
No.
@moyeenshaikh99152 жыл бұрын
@@ankitbansal6 how to imagine sir which column to take on left table and which one on right
@ankitbansal62 жыл бұрын
@@moyeenshaikh9915 watch video again
@akashwatar6633 Жыл бұрын
1
@TechnoSparkBigData2 жыл бұрын
could you please share script to create these tables?
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;
@rishabhkesarwani5761 Жыл бұрын
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
@vignesh91732 жыл бұрын
I solved a similar problem on Leetcode today! Used sub queries for it!
@ankitbansal62 жыл бұрын
Cool
@vignesh91732 жыл бұрын
This was a really good explanation! I’ll definitely be following your videos! :D
@rajunaik8803 Жыл бұрын
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
@dikshachourasiya556111 ай бұрын
can't we use this condition on e.emp_id=m.manager_id ? Using this giving different output
@angelnadar12092 жыл бұрын
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 ?
@ankitbansal62 жыл бұрын
Sure I will check that question.
@the_class_apart2 жыл бұрын
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;
@ankitbansal62 жыл бұрын
In case of inner join both are same 😊
@raushansingh7530Ай бұрын
SELECT emp_name FROM emp_manager e WHERE salary > ( SELECT salary FROM emp_manager WHERE emp_id = e.manager_id ); this is another approach
@manjumohan7731 Жыл бұрын
@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;