1 00:00:00,300 --> 00:00:04,520 In this video, we're going to take a look at the outer join. 2 00:00:04,520 --> 00:00:07,480 Outer joins are quite as common as inner joins. 3 00:00:07,480 --> 00:00:11,000 But they come in quite handy for more complex queries. 4 00:00:11,000 --> 00:00:14,980 >> Remember that an inner join will return rows from two tables 5 00:00:14,980 --> 00:00:18,520 where the data matches on both sides of the relationship. 6 00:00:18,520 --> 00:00:22,360 An outer join also brings back data from two tables. 7 00:00:22,360 --> 00:00:26,010 But instead of just returning the results where the data matches, 8 00:00:26,010 --> 00:00:28,030 the center of this venn diagram. 9 00:00:28,030 --> 00:00:32,130 Outer joins return records that don't necessarily have to match, too. 10 00:00:32,130 --> 00:00:35,190 There are three types of Outer Join. 11 00:00:35,190 --> 00:00:37,040 Left, right, and full. 12 00:00:37,040 --> 00:00:41,380 The Left Outer Join returns all data from the table on the left. 13 00:00:41,380 --> 00:00:45,150 And only the records that much in the table on the right. 14 00:00:45,150 --> 00:00:48,910 The right Outer Join is just like the left Outer Join. 15 00:00:48,910 --> 00:00:50,773 But the tables are flipped. 16 00:00:50,773 --> 00:00:53,707 We'll get all records from the right table. 17 00:00:53,707 --> 00:00:57,090 And then match records from the left table. 18 00:00:57,090 --> 00:00:59,760 Then we have a full outer join. 19 00:00:59,760 --> 00:01:03,360 This will match up all records that exist in both tables. 20 00:01:03,360 --> 00:01:09,130 And then return the remaining unmatched data from both left and right tables. 21 00:01:09,130 --> 00:01:14,430 Most, but not all, databases support all three outer join types. 22 00:01:14,430 --> 00:01:16,970 We're going to focus on the left outer join. 23 00:01:16,970 --> 00:01:18,900 But be sure to check the teacher's notes for 24 00:01:18,900 --> 00:01:22,580 more information on all other types of outer joins. 25 00:01:22,580 --> 00:01:25,380 Now, let's see it in action. 26 00:01:25,380 --> 00:01:28,280 Let's look at our make and model tables again. 27 00:01:28,280 --> 00:01:31,220 Let's say we want to answer the question. 28 00:01:31,220 --> 00:01:34,050 How many models of each make are there? 29 00:01:34,050 --> 00:01:37,470 Let's first take a look at our Make Table. 30 00:01:37,470 --> 00:01:41,390 Pay attention to one make in particular, BMW. 31 00:01:41,390 --> 00:01:46,330 If we inner join the make and model table, like we did in a last video. 32 00:01:46,330 --> 00:01:49,950 We'll see that BMW disappears. 33 00:01:51,390 --> 00:01:55,880 This is because BMW is only in our Make table. 34 00:01:55,880 --> 00:01:59,310 But we have no BMW models yet. 35 00:01:59,310 --> 00:02:03,240 BMW doesn't show when we use the INNER JOIN. 36 00:02:03,240 --> 00:02:06,496 Let's change that to aid LEFT OUTER JOIN. 37 00:02:06,496 --> 00:02:09,880 This will get all rows from the Make table, 38 00:02:09,880 --> 00:02:13,640 regardless of whether it has a match in the model table. 39 00:02:13,640 --> 00:02:17,720 Now we see one row with a make of BMW in the results, 40 00:02:17,720 --> 00:02:20,110 with null in the value for the model. 41 00:02:20,110 --> 00:02:22,650 Note that when doing a left outer join, 42 00:02:22,650 --> 00:02:26,540 the order in which you specify the tables is important. 43 00:02:26,540 --> 00:02:30,660 The database engine will look for rows from the left table, 44 00:02:30,660 --> 00:02:36,300 which is the one you specify first, immediately after the keyword FROM. 45 00:02:36,300 --> 00:02:41,330 Finally, let's count our models, 46 00:02:41,330 --> 00:02:45,880 and alias it as number of models. 47 00:02:51,490 --> 00:02:58,468 Not forgetting to group by their common attribute. 48 00:02:58,468 --> 00:03:01,833 Which is then MakeName. 49 00:03:01,833 --> 00:03:08,040 As you can see, B.M.W. has a zero count. 50 00:03:08,040 --> 00:03:11,970 That's because the count function does not count null values. 51 00:03:13,540 --> 00:03:18,860 The left outer join includes B.M.W. since it's in the table on the left make. 52 00:03:21,090 --> 00:03:31,291 However, if we did an inner join, B.M.W. would vanish 53 00:03:31,291 --> 00:03:34,000 and this report would be incorrect. 54 00:03:34,000 --> 00:03:36,500 Knowing the difference between an inner and 55 00:03:36,500 --> 00:03:40,256 outer join can help you produce better and more accurate reports. 56 00:03:40,256 --> 00:03:45,006 To write a left outer join statement, first, we need to select 57 00:03:45,006 --> 00:03:50,560 the columns from both the tables you want to return in the result set. 58 00:03:50,560 --> 00:03:55,140 Then the FROM keyword with the first table on the left. 59 00:03:55,140 --> 00:03:57,630 Then, the keywords LEFT OUTER JOIN 60 00:03:57,630 --> 00:03:59,300 and the second table name, 61 00:03:59,300 --> 00:04:02,700 with the ON keyword and equality criteria. 62 00:04:02,700 --> 00:04:07,330 Then, any additional clauses like a WHERE clause or a GROUP BY clause. 63 00:04:07,330 --> 00:04:12,930 Like an inner join query, left outer joins can be combined with many of the joins, 64 00:04:12,930 --> 00:04:14,340 including inner joins.