LeetCode 181: Employees Earning More Than Their Managers [SQL]

Solution and walkthrough of leetcode database problem 181: Employees Earning More Than Their Managers. I'm using MySQL but this solution should work in any SQL dialect such as PostgreSQL SQL Server, etc.
Link to the problem: leetcode.com/p...

[@abinayen]

really helping me study, thank you for all the clear explanations!

[@shivambaghel9668]

you deserves more likes and subscribes

[@rudrapriyan5162]

Do you think if we EXISTS it would reduce the time complexity or the number of tuples it has to olerate on ?

[@VedPrakashLearnToGrow]

Awesome Explanation, Thank you

[@vgaurav3011]

Nice explanation, loved it!

[@angelalu3855]

very useful! thanks a lot!

[@zeeshanali271]

I still don't understand the ON clause position statement

[@rachcastellino]

when you are matching on managerID and ID, why does it pair together ID's that aren't the same value? is this supposed to create all combinations of employees?

[@frederikmuller]

the join takes the ManagerId of a row and looks for a matching (regular) Id. it seems like you mixed up ManagerId and Id. take a look at the output at 4:21 to see how the 4th and 5th column always match. managers also have a regular Id which can't match the regular Id of another employee since these Ids are unique. hope this helps :)

[@TheProximator]

Nice, thanks a lot :)

[@frederikmuller]

You're welcome!

[@yueleji8892]

could you explain the difference between using ' on and' and 'where' in left join? for example leetcode 577? Thank you!

[@frederikmuller]

good idea for an upcoming video!

[@semantosaini9600]

why in the on clause it is e1.managerid=e2.id and not e1.id=e2.managerid?

[@vinoddiwan5792]

because from e1 table first we need to find if its manager exists or not . If exists then match from main table e.i id . So, e1.manager id id the foreign key because we need to first find if it exists in other table or not.

[@mritunzaysingh8978]

create table employee (id int not null PRIMARY key, name varchar(20), salary int, managerid int ); insert into employee VALUES(1,'JOE',7000,3),(2,'MARRY',8000,4),(3,'SAM',6000,NULL),(4,'MAX',9000,NULL); select * from employee; select E.name,E.salary as emp_salary,M.salary as manager_salary from employee as E join employee as M on E.managerid = M.id where E.salary > M.salary; select E.name as emp_name from employee as E join employee as M on E.managerid = M.id where E.salary > M.salary;

[@avahome5285]

self join to get the employee's salary and the manager's salary in a row.

[@JesusReigns7]

Is this still in SQL?

[@alibhaiacademy2981]

Thanks, my solution SELECT name AS "Employee" FROM employee e WHERE managerid IN ( SELECT id FROM employee m WHERE e.salary > m.salary

[@mickyman753]

I wrote it like this , don't know why that join doesn't click when I'm doing problems , although I'm doing the same thing select e1.name as Employee from Employee e1 where e1.salary>(select e2.salary from Employee e2 where e2.id=e1.ManagerId);

[@arnabpersonal6729]

I see u like white a lot

[@frederikmuller]

yeah