1 00:00:00,210 --> 00:00:04,019 Next up, we have another challenge, and this one is a little bit different. 2 00:00:04,170 --> 00:00:08,100 I want us to identify the series that have no reviews. 3 00:00:08,310 --> 00:00:09,750 So how are we going to do that? 4 00:00:10,050 --> 00:00:14,010 Well, we need to use a join and it's not going to be an inner join. 5 00:00:14,130 --> 00:00:20,730 We'll need to join the series with reviews and then see where there are series that have no reviews. 6 00:00:21,720 --> 00:00:23,400 Where some value is null. 7 00:00:24,030 --> 00:00:27,960 So we'll do something like select. 8 00:00:27,960 --> 00:00:37,770 Let's just start with Star from series and then we'll do a join reviews, the reviews table, but that's 9 00:00:37,770 --> 00:00:39,030 going to be an inner join. 10 00:00:39,240 --> 00:00:49,500 We'll start with that and we'll say join on where the series ID is equal to the reviews dot series. 11 00:00:50,280 --> 00:00:51,000 I'd. 12 00:00:52,030 --> 00:00:55,540 If we do this, this only gives us the exact overlap. 13 00:00:55,750 --> 00:00:59,020 Only the series that have a review are joined with the review. 14 00:00:59,380 --> 00:01:05,319 But what I want is actually a left join in my case because I want all of the series information and 15 00:01:05,319 --> 00:01:08,080 then any corresponding reviews information. 16 00:01:08,080 --> 00:01:12,730 So if I change this to be a left join, otherwise keep it the same. 17 00:01:14,550 --> 00:01:16,290 Now we see something a little different. 18 00:01:17,160 --> 00:01:19,650 It looks all the same until we get right here. 19 00:01:19,680 --> 00:01:24,310 These two series have no corresponding reviews, so we get. 20 00:01:24,330 --> 00:01:25,530 No, no, no, no. 21 00:01:25,800 --> 00:01:30,730 So now all we need to do is only select the ones that have no there. 22 00:01:30,750 --> 00:01:32,640 We're trying to get the title right. 23 00:01:32,760 --> 00:01:34,380 Malcolm in the Middle, Pushing Daisies. 24 00:01:34,380 --> 00:01:35,340 That's all I selected. 25 00:01:35,340 --> 00:01:36,600 I don't want anything else. 26 00:01:37,230 --> 00:01:44,460 So I would do something like select from this table where we could say where rating is null, where 27 00:01:44,460 --> 00:01:50,520 series IDs review or ID, anything, where one of these is null, and I think I'll just do where rating 28 00:01:50,520 --> 00:01:51,300 is null. 29 00:01:51,480 --> 00:01:56,670 So where rating and I can't do equals remember with null we use is null. 30 00:01:59,790 --> 00:02:04,980 And now I've narrowed it down to these two rows where rating? 31 00:02:04,980 --> 00:02:06,060 I just picked one column. 32 00:02:06,060 --> 00:02:10,139 But where rating is No, that would indicate there was no matching reviews. 33 00:02:10,940 --> 00:02:12,740 And then I just need to select the title. 34 00:02:15,010 --> 00:02:16,360 And that should do it. 35 00:02:16,990 --> 00:02:21,250 I think I gave it an alias of unrevealed series, so let's add that in. 36 00:02:22,970 --> 00:02:26,720 As an reviewed series. 37 00:02:27,520 --> 00:02:28,150 Okay. 38 00:02:28,480 --> 00:02:31,370 So another situation where left join helps us, right? 39 00:02:31,390 --> 00:02:38,950 It reveals information or the lack of review for a given show that we wouldn't get when we use a inner 40 00:02:38,950 --> 00:02:41,170 join, which only gives us the overlap. 41 00:02:41,410 --> 00:02:46,780 Now I could rewrite this as a write join really quickly, just show that same thing. 42 00:02:46,780 --> 00:02:50,950 But instead of starting with series we would select. 43 00:02:51,130 --> 00:03:00,130 We could still do title as on reviewed series, but we'll select to start with from reviews. 44 00:03:00,780 --> 00:03:10,650 And then do a right join, because the right side will be the series table and then we'll do on series 45 00:03:10,650 --> 00:03:16,590 date ID equals reviews series ID where rating is null. 46 00:03:17,040 --> 00:03:23,520 And this should be equivalent because now we have series on the right side and we're doing a right join 47 00:03:23,520 --> 00:03:23,880 here. 48 00:03:23,880 --> 00:03:25,290 We had series on the left side. 49 00:03:25,290 --> 00:03:28,950 We did a left join and we get the same answers. 50 00:03:28,950 --> 00:03:31,080 Malcolm in the Middle, Pushing Daisies. 51 00:03:32,670 --> 00:03:34,260 Next up, another challenge.