Self Join in SQL

[@DatabaseStar]

Want to easily remember the SQL commands for your database? Get my free SQL Cheat Sheets here: databasestar.mykajabi.com/lpkj-sql-cheat-sheets/?

[@yawenxue2963]

I like how you talk :quiet and clear

[@DatabaseStar]

Thanks!

[@preciouschinenyenwa8490]

Thank you, I was struggling to understand self join, but after watching your video, I realised how simple it is.

[@DatabaseStar]

Glad it was helpful!

[@joel-rg8xm]

Man! That CATEGORIES example saved me just in time before creating many tables. Thanks

[@DatabaseStar]

No problem, glad it was helpful!

[@mdimranhussain171]

got here from a course i couldnt understand this self join and Thanks to you it's all clear now. you explained it really well with the perfect examples. Keep up

[@DatabaseStar]

Great to hear!

[@kim-ex5cn]

THANK YOU. i have read a hundred tutorials and was stuck on the alias. starting with the from and on clauses and explaining the AS is implied and then going back to the select cracked things open for me. much appreciated.

[@DatabaseStar]

Thanks for the kind words! I’m glad this video helped you understand it.

[@elioko3639]

Definitely needed this and you explained it perfectly

[@DatabaseStar]

Thanks!

[@Moon22m]

Thank you so much! I was trying to understand the ON statement in SELF JOINS and you've explained it perfectly.

[@DatabaseStar]

Glad it was helpful!

[@Kornackifs]

I struggled with an intermediate leetcode sql problem I didn't want to see the solution right away so i started searchin on sql topics to full the knowledge gap but what i found is actually a video solving the problem itself fuck me

[@DatabaseStar]

Oh is that a good thing or a bad thing?

[@Kornackifs]

@@DatabaseStar I don't maybe I should've solved it on my own 😥

[@gauravsrivastava17]

9:05 writing query and making er diagram is so much helpful

[@DatabaseStar]

Glad you found it helpful

[@kpamiose]

Thanks for breaking this down so simply.

[@DatabaseStar]

Glad you like it!

[@TK-ko3fg]

King of SQL explanation

[@DatabaseStar]

Thanks!

[@Kartik-vj5os]

Even after a year you are helping guys like me! Thanks for clearing my doubt

[@DatabaseStar]

Thanks! Glad you found it useful.

[@astorgadexterpelaez3502]

thank you! been lurking in your channel for a while now, great content sir!

[@DatabaseStar]

Awesome, thank you!

[@metaman8662]

I'm thankful I found this video your explanation is what I needed!

[@DatabaseStar]

I’m glad you liked it!

[@dhruvitmodi3043]

Nicely explain in simple terms...checked many of your videos too... I am being SQL developer, liked your work 👍

[@DatabaseStar]

Thanks! Glad you like the videos.

[@thanzeeljalaldeen]

somethething that i needed currently for my project. thanks a lot sir

[@DatabaseStar]

Glad it was helpful!

[@OberynPinkViper]

Thank you for the clear and simple explanation. I remembered seeing the Employee - Manager example somewhere a few years ago, but I couldn't recall where exactly I saw it.

[@DatabaseStar]

Glad it was helpful!

[@aiasaiascon3894]

I have a question again - Using the Aliases is really flexible and clever - when you deal with a big database - do you use any method to keep a list of the aliases that you have created ? In case they are quite many ?

[@DatabaseStar]

Good question. I don't have a method to keep a list, but the aliases only exist for a specific query so we can see that they refer to right next to where they are defined.

[@ExcelTutorials1]

This is super helpful! Your voice is almost like listen to ASMR, lol. Nice to listen to

[@DatabaseStar]

Heh thanks- I’ve been working to improve the audio on my recent videos so the older ones may sound softer.

[@teenytinytoons]

It's not almost like, IT IS!!!

[@quynhanhnguyenthi1686]

Thank you so much for this clear explanation

[@DatabaseStar]

Glad you liked it!

[@RoysIdea]

So how would you expand these queries if you want to order them from top to bottom, with a level or additional spaces? So start with Clair (who has no manager), followed by a manager and his/her team, etc. And include a level column.

[@DatabaseStar]

Good question. It depends on the database vendor, and I've written more about it here: www.databasestar.com/hierarchical-data-sql/

[@slimandahra4294]

your explaining is great thank u..and good luck

[@DatabaseStar]

Thanks!

[@chesterliu1929]

At 6:38, relationship between id and manager_id is one to many which means one manager_id can have multiple employee_id to manage. However the multiple arrow sign is pointing to manager_id which should mean many manager_id can manage one employee_id. I think the arrow should be the opposite way, right?

[@DatabaseStar]

Ah yes you're right! The arrow should be the other way around. Thanks for letting me know.

[@offlinedie]

@@DatabaseStar Hi! Sry I think you were correct in the video, Since the foreign key is always on the many side in a EDR. Right?

[@jaymo2024]

04:45 the ON clause is what you need to know the rest is bloatware

[@DatabaseStar]

Thanks for the comment!

[@shriefabdelaziz4280]

wow amazing explanation thanks!!

[@DatabaseStar]

Glad it was helpful!

[@aakash1763]

Great explanation just one doubt in the ER diagram for both examples of self join why did you show 1:many relationship?

[@DatabaseStar]

Good question! It's because of the way the two concepts or records are related. For example with employees and managers, an employee has one manager, but a manager can have many employees. This is a one-to-many relationship, so I've drawn it that way on the diagram. Hope that helps!

[@Maria-n5g1l]

Huge thanks for such a clear explanation! Helped a lot!

[@DatabaseStar]

Glad it helped!

[@Mahde]

Brilliant explanation

[@DatabaseStar]

Glad you liked it!

[@BronProject]

Thank you for this!

[@DatabaseStar]

Glad it helped!

[@hyperborean72]

lovely example

[@DatabaseStar]

Thanks!

[@ZenonMcRae]

Nice & clear

[@DatabaseStar]

Thank you

[@matze489]

really good video

[@DatabaseStar]

Thanks!

[@Eyenn_n]

Great content! Awesome channel!

[@DatabaseStar]

Thanks so much!

[@kumruorkun3947]

Very clean, thank you

[@DatabaseStar]

Thanks!

[@oscarpalomares8282]

Great explanation!

[@DatabaseStar]

Thanks!

[@SonAyoD]

Great video

[@DatabaseStar]

Thanks a lot!

[@AmanRaj-zo7bx]

in ERD Diagram, id to manager_id relationship is One to Many, which means an employee can have multiple managers but one manager can not have multiple employees !! But it's not the case here, please clarify it.

[@DatabaseStar]

Good point, the employee can only have one manager but the manager can have many employees.

[@AmanRaj-zo7bx]

@@DatabaseStar Thanks for clarification.

[@luizetulio4888]

Great video. Is it possible to use self join and a many to many relationship simultaneously or it would work only for one to many? For example if an employee has 2 or more managers...

[@DatabaseStar]

Good question! Yes, that is possible. Another example of using that could be if you want to capture the parents of a person, where each person could have more than one parent.

[@osoriomatucurane9511]

The concept is easy and straitforwad, but times come you face a question with no clue that the solution comes with self join, unless you are given the schema

[@DatabaseStar]

Thanks! That is a good point.

[@enricoroselino7557]

and what if there is a period validity of the structure (include year and month column) ? should i put it on the same table ? is it okay to have duplicate rows of ppl name?

[@DatabaseStar]

Good question, I think it's OK to have on the same table.It would then be an additional condition on your join, or in the WHERE clause, to check if a record falls in a certain date range.

[@bouslamafathi3581]

Perfect explanation thanks a lot

[@DatabaseStar]

You are welcome!

[@MDARUN-ph1dw]

No wonder why you are calling yourself a database star 🔥🔥🔥🔥😁

[@DatabaseStar]

Thanks! The name is more for my readers and viewers to become "database stars" by learning and improving :)

[@MDARUN-ph1dw]

@@DatabaseStar 😁😁🔥🔥🔥🔥🔥🔥🙏🏻🙏🏻

[@balaji2035]

hi sir I have one doubt if we specify e.manager_id =m.id it will give the result only the both columns having same values right example manager_id =1 and emp_id also 1 or how does it works can you please clarify my doubt.

[@DatabaseStar]

Yes, if you match on e.manager_id = m.id it will show employers and their managers, which is correct.

[@lehadypierrengangue4149]

thanks for the content !!

[@DatabaseStar]

No worries!

[@humbertojesustoledovazquez5891]

What a video!!! Thanks :)

[@DatabaseStar]

Glad you liked it!

[@michaelchiz8055]

Good tutorial

[@DatabaseStar]

Glad you think so!

[@spaaaaace8952]

Thank you.

[@DatabaseStar]

You're welcome!

[@newbeginning3089]

Thanks. It was 👍

[@DatabaseStar]

You're welcome!

[@olufemiolawale3848]

Thank you sir

[@DatabaseStar]

You're welcome

[@rural_world]

Perfect

[@DatabaseStar]

Thanks!

[@youmightdontknow450]

Thank you sir

[@DatabaseStar]

Glad it helped!

[@trytofindareasonwhy]

self joins make my brain melt

[@DatabaseStar]

Yes they are a hard concept to understand!

[@Dobby_zuul]

I think your ERD should be the other way around

[@DatabaseStar]

Which one, the employee and manager? I think it's correct - an employee ID can be related to many other employee manager ID fields, which indicates that an employee can manage multiple other employees. Unless I am missing something?

[@gauravsrivastava17]

❤

[@DatabaseStar]

Thanks

[@souronion3822]

It is okay

[@DatabaseStar]

Thanks!

[@haarkee9366]

I love you

[@DatabaseStar]

Haha thanks!

[@poojamore056]

Hey, the employee example was good! I waited till end for another example tho.. 🥲

[@DatabaseStar]

Thanks! Good point, I should have included more examples.

[@arnosolo2008]

Thank you.

[@DatabaseStar]

You're welcome!