 What's going on everybody? My name is Alex Freeberg and today we're going to be starting our intermediate SQL series. If you joined us for our last series, we walked through the basics of SQL, which is everything we needed just to get started. And in this series, we're going to be walking through some intermediate concepts to really take your skills up to the next level. Now, today we're going to be walking through joins, but let me show you what you can expect from the entire series for this intermediate course. So we're walking through joins today. And then in future videos, we're walking through unions, case statements, updating and deleting data, partitioned by data types, aliasing, creating views, having versus the group by statement, the get date function, primary care versus foreign key. And then we're going to have an advanced course. And this is not set in stone yet. But these are some of the things that I think I will be going through or walking through. We're going through CTEs, sys tables or system tables, sub queries, temp tables, string functions, regular expression, store procedures and then importing and exporting data. So with all that being said, let's get into it. All right, now let's get rid of me because we do not need to be seeing me for the rest of the series. At the very top here are some of the things that we're going to be going through today, which are inner joins and then outer joins and then the outer joins. We have a few different styles or three different types of outer joins. Now, a join is a way to combine multiple tables into a single output. For now, we're going to be using the employee demographics and the employee salary table. So let's get a look at both of these tables and see what's in them. In our employee demographics table, we have employee ID, first name, last name, age and gender. And then down here in our employee salary table, we have employee ID, job title and salary. If you notice they have a similar column and that's going to be the employee ID. Now, when you're doing a join, you have to do this based off a similar column and typically you want it to be a unique field. So we're going to be using the employee ID from both tables to join these tables together to create one output. So let's get rid of this real quick and let's start building our query to join these two tables together. So the first thing we're going to do is an inner join. So let's do select everything and let's do it from SQLTutorial.dbo.employeeDemographics and let's do join. We can also say inner join, but join by default is going to say inner. And we're going to do SQLTutorial.dbo.employeeSalary. Now we have to join them together, which is what we talked about earlier and we're going to be doing that based off the employee ID. So for that we have to say on and then we're going to say employeeDemographics.employeeID is equal to employee salary. So let's run this real quick and take a look at the output and let me pull this up real quick. So what we are looking at is actually both tables combined. We have the employee ID first name, last name, age, gender and then here's the salary, employee ID, job title, salary. Now an inner join is really only going to show everything that is the same. So in both tables there are employee IDs of 1001 all the way down to 1009. But if you notice there is data that is missing. Real quick let's go down to this graphic and let's look at this inner join. An inner join is going to show everything that is common or overlapping between table A and table B. So what we are looking at here is exactly that. We are only looking at the things that are similar based off this employee ID in both tables. Now let's change this join to a full outer join and let's run this and see what we get. Now if you notice the output is very different. So let's take a look at it and see why it's so different. If you notice everything down to here is the exact same. So employees 1001 down to 1009 are exactly the same. But once we get down to row 10 it starts to get very different. Now we are joining these tables based off the employee ID. So for example right here Ryan Howard has an employee ID of 10011. But as you can see in this table for salaries there is no 10011 employee ID. So it has nothing to link it to. So because of that it fills in everything as null because it has nothing to match on this table. And vice versa in the employee salary table there's a person in here that's a salesman and there's no employee ID at all which means all this information is going to be null. And we can see that in this diagram right here. So this is the full outer join right here. And what it is saying is we are going to show everything from table A and table B regardless of if it has a match based on what we were joining them on. So even if table A has an employee ID but there's no employee ID in table B we're still going to show it and vice versa. So now let's look at a left outer join. A left outer join is going to take the left table and say we want everything from the left table and everything that's overlapping but if it's only in the right table we do not want it. Now what is the left and the right table? The left table is going to be our first table that we use. Our right table is going to be the second table that we use. So we're going to look at everything in the employee demographics table regardless of whether or not it has a match on the employee ID in the employee salary table. So this is what that looks like. So as you can see this is our entire table for employee demographics. And down here we have three that have information in the employee demographics table but have absolutely no information in any of the employee salary table because there's nothing to match it on. So this 1011 is not in this table. This 1013 is not in this table. And this one does not even have an employee ID. So we're not going to have a match at all. And if we change that to the right you'll see the exact opposite. It's going to show us everything in the employee salary table. So now we have all of our information right here from the employee salary table. And if it doesn't match in this table it's just going to get nulls. So down here we have 10010 and obviously there's not going to be anything associated with that because there's no 10010 in the employee demographics table. And for this one we have a salesman with no employee ID and since there's no employee ID to tie it to this demographics table we're going to have nothing. And we can see that in the diagram right here. So for the left outer join we're looking at everything in table A which is our demographics table and in our right outer join we're looking at everything at table B which is our salary table. Now let's pull this down a little bit. So so far we've only been using the select star. So we've been selecting everything and I only did that just for demonstration purposes but you most likely would not be doing this when you actually use these joins. What you're probably going to want to do is select exactly what columns you want in your output. So for example let's do employee ID, let's do first name, last name and let's do job title and let's do salary. And let's try to run that really quick. And as you can see it is not going to work. Now why is that not working? It's not working because we have two fields, one in each of these tables and we have to specify what employee ID we want because that is going to drastically change what our output is. So we have an employee ID in this table and in this table which one do we want to use. So for this demonstration let's use employee demographics dot employee ID and let's actually just do an inner join because it's easier for the output. Now let's run this and see what we get. So as you can see we now have the employee ID, first name, last name, job title and salary. Now we're doing this with an inner join based off the employee ID from the employee demographics table but if we use the employee salary table it should give us the exact same output and that's because we're using an inner join and an inner join is only going to show us everything that overlaps between both tables. But now let's try a right outer join and let's run this. Now we're using this employee ID from our employee salary table and since we're doing a right outer join we're going to get all the information from our employee salary table and it does not have to be in our left table which is our employee demographics table. So if you look at the information down here this 110 is in the employee salary table but it's in this position because that's what we're looking at in our select statement. And then over here we have our salary and since we have information right here which is in our employee salary table but there is no employee ID, our employee ID is null. Now let's change this to look at the employee demographics employee ID and execute it. As you can see that 110 is gone. Now we just have this information right down here and we didn't have the employee ID for either of these so it's going to show it regardless and that's again because we have a right outer join and that's why we have no employee ID down here. Now let's do a left outer join and it's basically going to do the opposite of what we just looked at. Now we're looking at everything from our left table regardless of if it's in our right table. And so our left table is our employee demographics table and we are looking at our employee demographics ID. So with the employee demographics ID it's going to show us the first name and the last name which is everything in our left table our employee demographics table and since for these IDs or lack of IDs it's just going to give us nulls in all of these places. If I change it right up here to the employee salary employee ID and I execute it because we are showing everything from our left table which is our employee demographics table we are still going to see our names but since we're using the employee ID from our right table now we're just going to have blanks in this information and this information. Now let's look at a use case for these joins. Let's say Robert California is pressuring Michael Scott to meet his quarterly quota and Michael Scott is almost there he needs like a thousand more dollars and he comes up with the genius idea to deduct pay for the highest paid employee at his branch besides himself. So how does he go about doing this and identifying the person that makes the most money? Well of course he's going to come to SQL first. So we actually want to look at a full outer join real quick and let's just look at everything. So here's what we have. We have the employee ID first name last name age gender employee ID job title and salary. Now what information do we need to know to get the information that Michael Scott needs? Well we need the employee ID. We want the first name and last name so let's write all that real quick. So employee ID we need first name we need last name and then we're also going to need the salary because we need to know who is the highest paid employee. So now let's do an inner join because we really only want to look at the employee IDs where we know what their name is and their salary is and let's do this based off the employee demographics table. Really doesn't matter for an inner join but let's do that real quick. So let's look at this. So we have our employee ID we have our first name our last name and our salary and we want to do it where it's not Michael Scott and that's because Michael doesn't want to take away his own money. He wants to take away his employees money. So let's do where first name does not equal Michael and he knows that he's the only one that is not named Michael. So now we have our list and let's do order by and let's do salary and let's execute this and let's do descending so that we can get at the very top and this is tough tough news for Dwight Schrute because it looks like he is the highest paid employee besides Michael and so it looks like he is going to get a cut in his pay this quarter so that Michael can meet his quota. So that's just one use case. Let's look at one more use case. Let's start out by getting rid of this and looking at everything again. So for our next use case, Kevin Lone who is an accountant thinks that he may have made a mistake when looking at the average salary for our salesman. Now, Angela Martin is very good at SQL and so what she is going to do is she wants to go in and calculate the average salary for our salesman. So let's try to get that information. So all we're going to need is the job title and the salary. So let's come up here and let's get job title and let's get salary and let's look at this and now we only want to look at where the job title is equal to salesman. Now the very last thing we want to do is we want to say we want the average of salary. Now, since we're going to need to do a group by we're going to have to get rid of this salary and just take job title right down here and do group by job title. So we're going to have job title and then the average salary and there you go. We have the salesman and the average salary is 52,000. So Angela now knows to go back and fix what Kevin made a mistake on. So that's how you use joins. I will include this image in the description so you can go and look that up yourself if you are curious and want to look at that. That really helped me out when I was first getting started to kind of conceptualize and understand what kind of data I was pulling based on what join I was using. So I hope that was useful to you as well. In the very next video, we're going to be looking at the union. So if that is posted, be sure to check that out next. Thank you guys so much for joining me. I really appreciate it. If you like this type of content or got anything out of it today, be sure to smash the like button, smash the subscribe button, and I'll see you in the next video.