All right, so if you're keeping count at home, we've gone two important joins so far. In this video, we're going to go over few other core joins, including the left join, the right join and full outer join. However, before we get started, I just want you to know that only the left join is available in SQLite which as you know is what we're using in this course. The right and full outer joins are definitely found in other database management systems which is why we'll go over them in this video as well. After watching this video, you should be able to, explain how a left join, right join and full outer join work. Identify how situations where it makes sense to use one join over another and use these joins to combine data from multiple tables. Okay, we're going to start with the left join. First, because it's pretty universal to most database management system including SQL Lite, so the easiest way to understand what a left join is going to do, is to look at the picture in the diagram here. The left join is going to return all the records from the table on the left side. So the table you first state and the matching records with the second table. So if you look at the customers you had, who had an order and let's say you have a customer who has given you their information but they haven't place an order. And so if you did an inner join on a table 1, which was customers and table 2, which was orders. And you didn't inner join, you'd be missing the customers who haven't place an order. So what this is going to do is it's going to allow you to say, hey, I still want everything from the customer table. I don't care if they didn't have an order but if they did, and then bring it also all together and bring it in in one order table. So that's the left join. The right join is very similar except in this instance, if we still stick with customers tables is on the left and orders is on the right. What this would do is pull in all the orders whether or not a customer is associated with that order or not. And then, for the ones that do have a customer most associated with it, it would pull in those records as well. So a little bit different. Really be careful if you are using left and right joins, and which table you're listing as coming first and make sure that relates to the left or right in the type of join you use. For the full outer join, this will return all the records where there is a match in either table 1 or there's a match in table 2. So this is just saying, hey, just return and give me everything whether there's matching one or matching two. So we'll go into a little bit more detail about these joins, we'll use this frequently, so we'll want to make sure we really understand them. The first one is let's say, again, we're talking about customers who have an order right? So this one, we want to get a list of all those customers whether or not they had an order and if they did have an order, we want that information as well. So, we just want our company name from the customers, the order ID. And then we'll say where it's from customers, which were pre qualifying that C. We're going to do a left join to the orders table which is O and the again, our link is that which is on C.CustomerID equals O.CustomerID. So this is really what's pulling it together in the middle section. But what you'll notice is what we listed out as first. So we're going to be gaining back all the customers because we're doing a left join and that's what we have listed first. So go ahead and play with this. This will be really fun when you're experimenting. Look and see what you get back when you list the customers first versus the order first. And that's a good way to test your join and see what's happening. Depending on your data, every customer may have an order. So you may get the same results but maybe every customer doesn't have an order. So again, it all comes to knowing and understanding the data you're working with. So for the right Join, what we're looking for in this is we want to return a statement with employees and orders that the employees may be associated with. So for this, again, I'm pre qualifying my names except I'm not pre qualifying it in this instance as O, I'm listing them fully out. So you can see how it gets a little bit longer in what I'm writing. I'm just going back and forth and showing you the different ways to do this but still getting the same results. So I have my columns right after my OrderID, LastName and FirstName. I´m getting this from the orders table, and then I´m going to right join the employees on orders. And this is going to be on the same EmployeeID from the orders table, equals the EmployeeID from employees table. So you might ask yourself is, well couldn't I have done the same thing and done a left join but just switched up the order of the tables? So instead of saying I want a right join where I'm getting all the employees back, where they could have had a match or not, I could have just listed the employees first and done a left join. And this is true, so good thinking. I'm glad you were paying attention. This is a 100% true. You can just switch the order of that. And that is why it's not a big deal that SQLite does not support the right joins, because if you need to do this type join, you can just switch the order of the tables and do a left join. But still something to be aware of in case you come across it in other queries that you might be reading or using from a different relational database management system, so you'll be able to use it. Okay, so now the full outer join. Again, this is not supported by SQLite which we will be using. But I just want to touch on it briefly in case this is something that you come across in a different database management system. In this example, the select statement is going to select all the customers and all orders. For this, again, we pre qualify our column names, we say where we're getting it from. We're getting it from the customers table. We say the type of join we're doing, full outer join to the orders and then we list what we're joining it on. Again, it all follows a similar syntax, just be aware of the type of join that you're using. So that's our more advanced joins, left, right, full outer joins. They all have their particular use cases. Just be careful when you're using them in the way you intend to, in order to get the results you expect. We have one more important though seldom used joint to talk about and we're going to cover that in the next video. See you there.
