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 );

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!

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

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

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☺

Great way of explaination by putting the same table next to the original table. Thanks!!

Great. Ager long years I am in the correct KZfaq channel for SQL. Thanks Ankit.

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

Great Way to explain with Excel not many would explain in Detail like this !

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.

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

You make SQL look so easy, really good. Thanks mate

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

Amazing Amazing seriously Amazing .... 👏 👏 👏 Thank you so mych

Most simple explanation. Thanks

as usual, great content. You have been a guru to us. Thanks for your time. It is a selfless act. Thanks again

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

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

That's how manager have grudges against some employees, they use Ankit Bansal's query. :)

great way of explaining. kudos!

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

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

very crystal clear and detailed explanation

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

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

Thanks Ankit, Great Content❣

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

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 );

I have solved this question same way in leetcode

Best explanation!!!!

Thanks for so clear explanation. Understood

Thank you sir. For the explanation.very clear

Very nice explanation

Thanks for making it so easy

thank u very easy

Great explanation 👍

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 );

Very helpful concept 👍🏻

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

Well explained, thank you so much. Subscribed !

great, thanks for the explanation

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

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?

Very well explained brother.

Beautifully explained

Clear explanation! thank u!

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

Superb!!!!

Very nicely explained

lovely explaining

please explain the reason for the join connection based on a.manager_id = b.id? why we cant use a.id = b.manager_id?

grt work sir

Thank you so much

Done

thank you so much dude you're a god

Thanks

Completed ❤

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?

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

nicely explained!

Hello Why is manager id taken from the employee table And not manager table? Thanks

Can we combine different columns using inner join

Thank you sir..

Hello Sir, You mentioned self join but in the answer you used Inner Join

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

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.

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

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?

please increase ur voice ..just now i subscribed

3/122

Can you please give more real time scenarios where Self Join is used?

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

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

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

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.

god bless u xdd

when I run the query say table does not exit

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.

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 .

Why did he use INNER JOIN and not SELF JOIN? Please someone tell me!

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

1

could you please share script to create these tables?

Please share the script to make the table.

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

I solved a similar problem on Leetcode today! Used sub queries for it!

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

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 ?

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;

SELECT emp_name FROM emp_manager e WHERE salary > ( SELECT salary FROM emp_manager WHERE emp_id = e.manager_id ); this is another approach

@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;