Self Join in SQL

📝 Get my free SQL Cheat Sheets: www.databasestar.com/get-sql-...
🎓 Learn and become confident in SQL: databasestar.mykajabi.com/sql...
A self join in SQL is where you write a query on one table that joins to the same table a second time. It's used when you have a record in the table that refers to another record in the same table.
A common example is employees and managers, or product categories. Anything with a "hierarchical data" setup may use an SQL self join.
This video shows you what an SQL self join is, how to write one, and how to overcome issues with column names and missing data.
You can find out more about joins overall in this guide here: www.databasestar.com/sql-joins/
If you want more database training, practice questions, and guides, enroll in Database Star Academy here: www.databasestar.com/dsa/
If you have any suggestions for other videos or comments on this one, let me know in the comments below.
Timestamps:
00:00 Introduction
00:12 Self Join definition
01:38 Employee solution
06:03 Final query
06:45 Categories example
07:30 Left Join

[@yawenxue2963]

I like how you talk :quiet and clear

[@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!

[@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!

[@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!

[@elioko3639]

Definitely needed this and you explained it perfectly

[@DatabaseStar]

Thanks!

[@joel-rg8xm]

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

[@DatabaseStar]

No problem, glad it was helpful!

[@Kartik-vj5os]

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

[@DatabaseStar]

Thanks! Glad you found it useful.

[@kpamiose]

Thanks for breaking this down so simply.

[@DatabaseStar]

Glad you like it!

[@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 😥

[@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!

[@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.

[@user-xs3kf9tx1m]

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

[@DatabaseStar]

Glad it helped!

[@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!

[@quynhanhnguyenthi1686]

Thank you so much for this clear explanation

[@DatabaseStar]

Glad you liked it!

[@slimandahra4294]

your explaining is great thank u..and good luck

[@DatabaseStar]

Thanks!

[@BronProject]

Thank you for this!

[@DatabaseStar]

Glad it helped!

[@Eyenn_n]

Great content! Awesome channel!

[@DatabaseStar]

Thanks so much!

[@ZenonMcRae]

Nice & clear

[@DatabaseStar]

Thank you

[@kumruorkun3947]

Very clean, thank you

[@DatabaseStar]

Thanks!

[@TK-ko3fg]

King of SQL explanation

[@DatabaseStar]

Thanks!

[@shriefabdelaziz4280]

wow amazing explanation thanks!!

[@DatabaseStar]

Glad it was helpful!

[@hyperborean72]

lovely example

[@DatabaseStar]

Thanks!

[@oscarpalomares8282]

Great explanation!

[@DatabaseStar]

Thanks!

[@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.

[@humbertojesustoledovazquez5891]

What a video!!! Thanks :)

[@DatabaseStar]

Glad you liked it!

[@lehadypierrengangue4149]

thanks for the content !!

[@DatabaseStar]

No worries!

[@bouslamafathi3581]

Perfect explanation thanks a lot

[@DatabaseStar]

You are welcome!

[@SonAyoD]

Great video

[@DatabaseStar]

Thanks a lot!

[@matze489]

really good video

[@DatabaseStar]

Thanks!

[@newbeginning3089]

Thanks. It was 👍

[@DatabaseStar]

You're welcome!

[@spaaaaace8952]

Thank you.

[@DatabaseStar]

You're welcome!

[@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!!!

[@michaelchiz8055]

Good tutorial

[@DatabaseStar]

Glad you think so!

[@olufemiolawale3848]

Thank you sir

[@DatabaseStar]

You're welcome

[@gauravsrivastava17]

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

[@DatabaseStar]

Glad you found it helpful

[@rural_world]

Perfect

[@DatabaseStar]

Thanks!

[@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.

[@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/

[@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.

[@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?

[@TheBoysZone]

This is how you should teach SQL.....step by step

[@DatabaseStar]

Glad you like the video!

[@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!

[@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.

[@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 😁😁🔥🔥🔥🔥🔥🔥🙏🏻🙏🏻

[@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.

[@trytofindareasonwhy]

self joins make my brain melt

[@DatabaseStar]

Yes they are a hard concept to understand!

[@souronion3822]

It is okay

[@DatabaseStar]

Thanks!

[@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.

[@gauravsrivastava17]

❤

[@DatabaseStar]

Thanks

[@jaymo2024]

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

[@DatabaseStar]

Thanks for the comment!

[@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!