Intermediate SQL Tutorial | Inner/Outer Joins | Use Cases
ฝัง
- เผยแพร่เมื่อ 4 ต.ค. 2024
- Take my Full MySQL Course Here: bit.ly/3tqOipr
In today's Intermediate SQL lesson we walk through Joins. Joins allow us to combine several tables, choose columns from each table, and use them in a single output.
All Topics Covered: Joins, Unions, Case Statements, Updating/Deleting Data, Partition By, Data Types, Aliasing, Views, Having Clause, GetDate(), Primary vs Foreign Key
____________________________________________
LINKS
Joins image: www.got-it.ai/...
____________________________________________
SUBSCRIBE!
Do you want to become a Data Analyst? That's what this channel is all about! My goal is to help you learn everything you need in order to start your career or even switch your career into Data Analytics. Be sure to subscribe to not miss out on any content!
____________________________________________
RESOURCES:
Coursera Courses:
Google Data Analyst Certification: coursera.pxf.i...
Data Analysis with Python - coursera.pxf.i...
IBM Data Analysis Specialization - coursera.pxf.i...
Tableau Data Visualization - coursera.pxf.i...
Udemy Courses:
Python for Data Analysis and Visualization- bit.ly/3hhX4LX
Statistics for Data Science - bit.ly/37jqDbq
SQL for Data Analysts (SSMS) - bit.ly/3fkqEij
Tableau A-Z - bit.ly/385lYvN
Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!
____________________________________________
Websites:
GitHub: github.com/Ale...
____________________________________________
All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for
TimeStamps:
Inner Join - 2:08
Outer Join - 4:08
Use Cases - 11:17
Hi,
I want to add more skills in my work.
I have a dumb question : "What the pros of SQL VS Excel(Pivot,Table and Query)"
I watched many videos and still don't understand what so special about SQL. I personally found its just more coding and Excel it's more "clicking" if I can use that word.
Hope you can help me to understand the Pros.
Thanks,
@@djfigmasta Excel is excellent but when it comes to big data or datasets excel may not handle that, now that's the point where SQL edges ahead.
Enjoyed the (use cases) so much. Thank you for your hard work
Thank you :)
i can see that there is some data you inserted in to after basic course
If anyone was looking for the new data in this video:
Insert into EmployeeDemographics VALUES
(1011, 'Ryan', 'Howard', 26, 'Male'),
(NULL, 'Holly','Flax', NULL, 'Male'),
(1013, 'Darryl', 'Philbin', NULL, 'Male')
Insert into EmployeeSalary VALUES
(1010, NULL, 47000),
(NULL, 'Salesman', 43000)
Thank you so so so much !!!! I was literally looking at those null's for the first 10 minutes of the video haha
My Dude!: As I rather copy paste then add in by hand haha
@@TheDefaultgameer I would suggest adding them using code. It would be a good revision.
Thanks Mate
thx friend, u'r a friend
was going through an SQL course as part of a certification from a top company, watched the video on join 4 times couldn't understand, came to Alex's free boot camp, and understood in one go. Your my hero
I have actually learned way more and remember concepts from your videos than the google certificate.
Is it a better idea to go through Alex vidoes on SQL before taking Google Cert?
@@Oluwagbeoyemiga I honestly felt the Google certificate was an extremely long intro to analytics class. Alex's videoes were straight to the point and way more informative
@@RandhawaAdi1995actually yes.. data analyst courses are too long comparing to his video
The Google course on joins is not very clear. Thank you for posting this! This helps a lot!
This makes me feel better lol. I cannot spend more money at the moment.
I was part of a paid bootcamp but I learned and understood the concepts a lot better from your free bootcamp than that paid one. Thanks a lot Alex. Highly appreciate your work.
I cannot thank you enough for taking the time of sharing this with the rest of the world. You are very talented!
I am done with the SQL Basics, moving to the intermediate, thank you so much Alex!
Woohoo!!
I tried many ways to understand the joins in a comprehensive way, but Alex was the only one that was properly explain it.
Thanks Alex!!
one of the best tutorial for data analyst on TH-cam. Been practicing the tutorial for a while now and figured the names used for the table was from my favorite show "the office". Thanks
Really love your style with these videos. Using the office as a template for looking at these problems and techniques is such a fantastic way of going about it.
I know right?
I have been using JOINS forever but this is the only time I officially understand it and will be able to explain it to others.
Understanding Joins in SQL has really been a challenge for me and I can say with this video, Its a solution. So exemplifying. Thanks Alex.
I've just came up after a online class beside that excellent explanation, as being a Office fan you made me smile. 😅
After struggling to understand joins today I came across this video and it was so simple and straight to the point . Shukran!
I cannot thank you enough for this simple explanation. I nearly exhausted my resources for JOINs and nobody ever explained well enough for me to understand.
Hi! Im Leandro from Argentina and I have just finished this lesson and please let me tell you that the practical and last part of the video was super clear and usefull. Keep up with the good work!
Hey Leandro! I'm so glad to hear that! I plan on making quite a few more of these in the near future :)
@AlexTheAnalyst, I want to take the time and thank you for making and sharing these videos. I am pursuing a degree in Information Systems, and I took a class in SQL, but your tutorial videos are easier to comprehend. I learned more in your short videos than I did in my class. You are amazing!!!!
just finished google data analysis certification and im here for more practice, im loving the way you explain things , im feeling more confident
Hello and congratulations on completing your GDAC. I am also taking the course and on the 5th course which has been a bit difficult which i believe is because of the particular instructor. If you don't mind, can we get in contact somehow so i can learn a thing or two from you and i hope you are also able to learn from as well as i get better. I wish you all the best in your journey.
SOME TROUBLESHOOTING INFO, maybe for someone it will be helpful. Hi Alex! Thank you very much for the courses. The beginners course is great. When I moved on to intermediate I understood that Im stucked a bit. In MySQL for instance FULL OUTER JOIN doesn't work, only LEFT OUTER JOIN or RIGHT OUTER JOIN. Then I got that in this course you use SQL Server Management Studio. For such beginners as me It could be nice to get the information in the beginning, that you use a slightly different version. I managed to install the SQL Server MS. But sadly I can't download the database Parks_and_Recreation into the SQL Server MS. it shows different errors, so there is no posibility to try all commands you use in your video. Maybe you read this comment to help others. THANK YOU VERY MUCH FOR WHAT YOU ARE DOING! YOU ARE A GREAT MAN!!!
I agree with this comment. Maybe someone in the community can help.
@@cobe2013 Hi. So I started with this tutorial: th-cam.com/video/8vTCyhDyRjg/w-d-xo.html in order to know how to install the SQL Server and create the same table, using the script, as Alex has. It worked.
@@cobe2013 there is another guy who explains how to connect to the server, because I had this trouble earlier, I installed the app, but didn't know how to connect to the server: th-cam.com/video/7zXtA0LwoHs/w-d-xo.html. I hope it's helpful.
Great work Alex.. !! Thank you for this explanation 😊
Bro, you explain things PROPERLY. Thank YOU.
Thank you! I am not an English speaker and I studied SQL during university, but I understood everything until now from you! Thank you Alex for this effort and for providing this content for free! I hope to use what I am learning in this Bootcamp in my career as a Data Analyst inshallah. And I loved how you gave Dwight a higher salary than The boss Michael. He would be happy if he knew that🤣 شكرًا أليكس
The explanation and diagram provided by you has nailed the concept in my brain
To whoever thumbs down this video, BLESS YOU AND I HOPE YOU FIND PEACE!
Thank you Alex for another wonderful lesson.
Thanks Alex for all you do, you're such a good teacher. Cheers
You explain complex stuffs and make it very simple. Big ups man
Watched the SQL Basics Tutorials and now on this in one day. Learned beter and faster and than in my school! cheers.
Before you start the video :
Insert into EmployeeDemographics VALUES
(null,'Holly','Flax',null,null)
(1011,'Ryan','Howard',26,'Male'),
(1013,'Darryl','Philbin',null,'Male')
insert into EmployeeSalary values
(1010,null,47000),
(null,'Salesman',43000)
Thank you!!
I spent two days trying to undertstand this, for some reason my head couldn't assimilate and i had to replay the video many many times, what helped me the most was to look for gifs explaining visually the join of two tables for each join type. Thanks for the video!
Your choosing example Tables helps alot to understand the concepts. Great teacher
Glad to hear that!
It is one of the best tutorials. Crystal clear explanation with use cases.
Great video, as a beginner it was actually easy to understand the concept behind each join
I'm having a hard time to get a good understanding on JOINS. Now I think I'm starting to get there. Thank you Alex!
Thank you for the easy to learn video. I knew some join basics but you enhanced my knowledge with your thorough explanations!
Great to hear!
LOL didn't know we were working with the office character name til you mentioned Micheal Scott. This just became interesting!
Teaching skill level: god
You're the best Alex
Thank you so much for your videos. I have been following you since November (I think) and you've been a huge part of my career change!! I am starting SQL this week in my data analytics bootcamp and I'm already struggling with JOIN 😓. Your videos are helping a lot! Also, I got a mug from your store, can't wait to receive it and start drinking my morning coffee out of it while coding. Who knows, maybe it will make everything clearer 😂
So glad to hear it! And that's super cool! :D I really do use it for my morning coffee haha
I’m so glad you explained what was the left and right table. I tried to Google and really couldn’t understand anyone’s explanation. But you made it so simple that I want to laugh at myself for not realizing the diff between left and right before. Thank you Alex
So glad I found this channel helpful stuff ; you da man
Very useful tutorials and the idea of the free Bootcamp is really great. I'm just so thankful for that, Alex. Keep up the good work!
Really great material regarding Joins from SQL SERVER. Useful
Thank you for this great and informative teaching Alex. I call you my unseen mentor who is constantly showing me the part to greatness. One day, I would make you proud In Nigeria and every household will know your name. THANK YOU BOSS
Took me a rewatch but I understand it now. Thanks Alex!
5:00 full outer join joins from both tables (all rows from left table and all rows from right table, even if they don’t have a match in the other table, the columns are filled with nulls)
Left outer join: return everything from left table, even when it doesn’t have match in right table, nulls are returned for the columns
Hello, im new to SQL , i tried but why there is no null in my table? :(please help
I've got same issue, have you managed to figure it out maybe?@@mariaseri8352
The image for the join part was awesome. Thank you very much for the great content
Alex thank you so so much. Im taking tje Google Analytics cert and you explained Joins easier. Plus being able to see the tables better helps.
You are the best! Thank you very much to share this bootcamp for free
Love the way you use office characters
Nice and clear instructions! love it
Thank you. Finaly I understand how to use Join(s) 😀
Wonderful! :D
Sir Alex,
First of all, thank you for these tutorials. They're very helpful.
For the second use case, I suggest you may use FULL OUTER JOIN instead of INNER JOIN because there's a data from the table EmployeeSalary which is missing in your results (a salesman who has a salary = 43000).
Thank you.
Thank you Alex!
Completed on April 2 2023
Could you please provide us with the data set so that we are able to follow the steps with you? Thank you in advance!
Have you followed the videos this step by step?
Absolutely excellent explanations. Thank you so much
Thank you so much, Alex!
Best SQL tutorials on TH-cam. Super clear!
Hey Alex,
This is the best video on JOINS that I have come across. Simple yet all covered.
Its Amazing !!! You made it so easy to understand !!
So glad to hear that!
This is the most helpful tutorial to understand Joins in sql. Very easy to understand the way you explain it. This is gonna help me at work coz I’m a newbie in sql. Thank you for sharing your knowledge!
Fantastic explanation of joins. Thanks!
Took the weekend off to practice the basic exercises on Stratascratch, back on for the intermediate ❤
Crazy how stratascratch qualifies Joins/Unions as Easy/Beginner level
YOU'RE THE BEST ALEX. THAAANKS.
Thanks for ur clearcut explanation
Haha I’ve been patiently waiting for this. Thanks so much
Thanks for watching!
This is great stuff! I plan on completing all of your lessons
I'm loving this.. ❤
I am waiting for the advanced part !
Fantastic video Alex, the use cases were hilarious
Man this is great really, I learned a lot with you and I am ready to learn even more. I am completing a bachelor degree in Finance but I wanted to change that since what I really liked about that field was the modeling aspect, I talked to my brother about that and he told me why don't you switch to data analysis or information system, I searched a lot of content on youtube and this channel is amazing, you're doing a great job, and motivated me to learn more about it being a data analyst and I hope I will make it in the future. Thank you for sharing your experience and your knowledge!!
Hey Salim! So glad to hear that! Looks like you've watched some other videos as well :D Glad you're enjoying the channel!
This is really helpful, thanks
I have to go over this again
Same here!
I just HAD to make this comment - did not expect one of Michael Scott's genius ideas to make an appearance 😂😂My laughing fit probably woke up my roommate. ROFL LMAO Best. SQL Teacher. Ever.!!!
Hi Alex! I am really fortunate to have passed by a tiktok video that promoted your channel as one that offers learning guides in topics relating to data analytics. Being a person who would like to transition to data analytics at 32 years old, I still have doubts with regards to the unknown particularly on the things that still needs to be learnt. I have already finished your Power BI for Beginners and now into SQL. Though I have already finished the Khan Academy lessons on SQL, I found that there are difference in the codes used in SQLServer compared to I think SQLlite in Khan Academy. Like there we used "TEXT" instead of "varchar(50)" and also we use double-quotations instead of single quotations. Your explanation helped me a lot in understanding the concepts of LEFT OUTER JOIN, FULL OUTER JOIN, RIGHT-OUTER JOIN AND INNER JOIN. There's one thing I am not sure of though. Why was INNER JOIN used on the last example? IS it because we are performing some sort of data cleaning and did not use salary data which does not have an Employee Demographic? Kudos and more power on your channel.
Thank you so much, your explanation is excellent.
Great stuff Alex !
loving your videos, very awesome and informative lessons. learning alot from your videos, make some videos on java
I know some Java, but I don't know enough or use it enough to do videos on it to be honest..
Thanks . You explained it very well and in simple terms.
How do you highlight, cut, paste so quickly? It looks like you're just clicking through your demos. It would be helpful to work more efficiently, "smarter not harder". Thank you for all your amazing, very accessible, and comprehensive work!
"Thank you guys so much for JOINing me" I see what you did there
lol an unintended yet enjoyable pun
I learned joins from the Google DA course through Coursera. This video is FAR better explaining it. However, Google's course touched on joins using different fields if you wanted to join 2 tables. Example: Lego Dataset, Star Wars builder sets table and parts table. Neither had the same column for an easy join like your employee demographics and employee salary example. Can you make a video explaining how to join tables with SIMILAR fields but not the same field? Thank you for all the videos you have put out!!
Alex the data ANALYST "HERO"
Thank you so mucj Alex for all the Video
Thank you so much!!!
Love your videos! However, in the final section here finding the average salary of a salesman you used an inner join which eliminated the salesman salary from the employeesalary table because it didn't have an employee ID. By not including the value (NULL, Salesman, 43000) , it skewed the average salesman salary by only evaluating those salesmen with matching employee ID's from both tables. Value should be 49750 not 52000 and you should have used a FULL OUTER JOIN instead.
Not quite. From a purely SQL standpoint this would make sense but this highlights some bad database design here. No primary key in either table is specified. Employee ID should be the primary key and it should be both unique and not null. If it was the PK, then that entry with a salary and job title without an employee ID would not be there. In a real world scenario this may represent the salary of an employee no longer working at Dunder and would then skew the avg salesman salary by being included.
@@zacpaolo7845 ohhh the burn :D
@@zacpaolo7845 In that case do you really think that joining is necessary here, you can just extract the avg salary from the employee salary table directly..
Thank you for this video awesome explanation
Thank you alex.
Great Job Alex
Great content learned a lot!!
Very informative!!
i think for the second case it's better to use Right Outer join because there is a salesman without an id in en the employeesalary table. thank's a lot for your teachings you are awesome.
On my journey through the boot camp...
I learned a lot here especially when I got an error "...is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Thank you Alex. Your videos have really been helpful. I am done with the basics, now on intermediate.
Love the explanation. I always had a hard time catching the differences between the join tables, your explanation made it very clear to understand. Loved the examples used at the very end of the video on how to utilize the tables to answer real-life problems.
Absolutely amazing content, Alex!! Thank you so very much!
Wow. This is really helping me understand joins. I use SQL Server Management Studio at work and have to know some SQL scripting for my job, but joins have been a little hard for me. This is helpful.
very well explained
Simple and Great!!!!
There are a lot more rows in the tables than there were in the previous video (and an extra table). Do you have a link for that code, so we can insert the values and add the table so we can follow along?
If anyone was looking for the new data in this video:
Insert into EmployeeDemographics VALUES
(1011, 'Ryan', 'Howard', 26, 'Male'),
(NULL, 'Holly','Flax', NULL, 'Male'),
(1013, 'Darryl', 'Philbin', NULL, 'Male')
Insert into EmployeeSalary VALUES
(1010, NULL, 47000),
(NULL, 'Salesman', 43000)
hi, great video tutorial !!!! Im currently working in the field of Finance and wonder how much SQL should I learn to be comfortable in the field? I would be so glad to hear from your opinion. Tks for the tutorial anw.
Bảo Trần Quốc I think to start go through the my basics guide and practice that until you feel confident in it. That is really the building blocks. The intermediate course is to take you to the next level with some more difficult topics, but the basics are used almost always while the next level topics aren’t always used. Hope that’s helpful! Good luck!
Alex! Please do Bring on the advanced tutorials 🙏
I already have some out! I'll have more coming out soon!
thanks alex.