Outer Joins

In this video, we're going to take a look at the outer join. 0:00 Outer joins are quite as common as inner joins. 0:04 But they come in quite handy for more complex queries. 0:07 >> Remember that an inner join will return rows from two tables 0:11 where the data matches on both sides of the relationship. 0:14 An outer join also brings back data from two tables. 0:18 But instead of just returning the results where the data matches, 0:22 the center of this venn diagram. 0:26 Outer joins return records that don't necessarily have to match, too. 0:28 There are three types of Outer Join. 0:32 Left, right, and full. 0:35 The Left Outer Join returns all data from the table on the left. 0:37 And only the records that much in the table on the right. 0:41 The right Outer Join is just like the left Outer Join. 0:45 But the tables are flipped. 0:48 We'll get all records from the right table. 0:50 And then match records from the left table. 0:53 Then we have a full outer join. 0:57 This will match up all records that exist in both tables. 0:59 And then return the remaining unmatched data from both left and right tables. 1:03 Most, but not all, databases support all three outer join types. 1:09 We're going to focus on the left outer join. 1:14 But be sure to check the teacher's notes for 1:16 more information on all other types of outer joins. 1:18 Now, let's see it in action. 1:22 Let's look at our make and model tables again. 1:25 Let's say we want to answer the question. 1:28 How many models of each make are there? 1:31 Let's first take a look at our Make Table. 1:34 Pay attention to one make in particular, BMW. 1:37 If we inner join the make and model table, like we did in a last video. 1:41 We'll see that BMW disappears. 1:46 This is because BMW is only in our Make table. 1:51 But we have no BMW models yet. 1:55 BMW doesn't show when we use the INNER JOIN. 1:59 Let's change that to aid LEFT OUTER JOIN. 2:03 This will get all rows from the Make table, 2:06 regardless of whether it has a match in the model table. 2:09 Now we see one row with a make of BMW in the results, 2:13 with null in the value for the model. 2:17 Note that when doing a left outer join, 2:20 the order in which you specify the tables is important. 2:22 The database engine will look for rows from the left table. 2:26 Which is the one you specify first, immediately after the keyword from. 2:30 Finally, let's count our models, 2:36 and alias it as number of models. 2:41 Not forgetting to group by their common attribute. 2:51 Which is then MakeName. 2:58 As you can see, B.M.W. has a zero count. 3:01 That's because the count function does not count no values. 3:08 The left outer join includes B.M.W. since it's in the table on the left make. 3:13 However, if we did an inner join, B.M.W. would vanish. 3:21 And this report would be incorrect. 3:26 Knowing the difference between an inner and 3:29 outer join can help you produce better and more accurate reports. 3:33 To write a left outer join statement, first, we need to select 3:40 the columns from both the tables you want to return in the result set. 3:45 Then the from keyword with the first table on the left. 3:50 Then, the keywords left outer join. 3:55 And the second table name. 3:57 With the on keyword and equality criteria. 3:59 Then, any additional clauses like a work clause or a group by clause. 4:02 Like an inner join query, left outer joins can be combined with many of the joins. 4:07 Including inner joins. 4:12