1
00:00:00,270 --> 00:00:00,690
Okay.

2
00:00:00,720 --> 00:00:03,240
Moving on to our next challenge with the same data.

3
00:00:03,270 --> 00:00:07,970
Something a little new and frankly refreshing to see such a short table.

4
00:00:07,980 --> 00:00:12,720
So what we're doing here is identifying the unrevealed series in our database.

5
00:00:12,720 --> 00:00:15,600
So there are a couple that nobody has written reviews for.

6
00:00:15,810 --> 00:00:19,710
For whatever reason, I haven't seen either of these shows really.

7
00:00:19,710 --> 00:00:24,570
I've seen Malcolm in the Middle maybe once or twice as a kid, but I don't feel like I can review it.

8
00:00:24,570 --> 00:00:27,150
And apparently nobody else in our app felt that way.

9
00:00:27,150 --> 00:00:34,110
So we have two unrevealed shows, and the point is not to cheat and to just use this information to

10
00:00:34,110 --> 00:00:39,030
find it, but to actually find it based off of the data we have in our database dynamically.

11
00:00:39,030 --> 00:00:39,960
So how do we write?

12
00:00:39,960 --> 00:00:41,010
It's going to be a join.

13
00:00:41,010 --> 00:00:46,050
What type of join do we need to write where we can see what series have not been reviewed?

14
00:00:47,370 --> 00:00:48,510
Well, let's give it a shot.

15
00:00:48,510 --> 00:00:54,060
We'll hop over to Cloud nine and we'll start with our handy dandy comments.

16
00:00:54,390 --> 00:01:00,450
We are on Challenge five and this one is an reviewed series.

17
00:01:01,330 --> 00:01:01,820
Okay.

18
00:01:02,770 --> 00:01:05,650
So what tables are we going to need to join here?

19
00:01:06,040 --> 00:01:15,130
So we've got if we go all the way back here, we've got reviewers, we've got series and reviews, and

20
00:01:15,130 --> 00:01:16,960
we don't need to care about reviewers.

21
00:01:17,440 --> 00:01:20,290
We don't care about names of who is reviewing things.

22
00:01:20,290 --> 00:01:27,610
What we care about is the overlap or lack of overlap between series, TV shows and reviews.

23
00:01:28,480 --> 00:01:41,050
So let's start with a simple join select and we'll do star from series and we can just start with that

24
00:01:41,050 --> 00:01:42,010
nice and simple.

25
00:01:42,160 --> 00:01:43,570
It doesn't tell us anything.

26
00:01:44,080 --> 00:01:48,550
We have no idea which ones have been reviewed or not, so we need to join it with the reviews.

27
00:01:48,550 --> 00:01:52,420
So that's going to be join reviews.

28
00:01:53,570 --> 00:01:58,220
On and it's going to be our typical join so series.

29
00:01:59,240 --> 00:02:13,580
Dot ID equals reviews dot series ID and let's make this explicitly inner just so we're clear there and

30
00:02:13,580 --> 00:02:17,030
when we do this we get a lot right.

31
00:02:17,660 --> 00:02:21,290
We see Archer with an idea of one.

32
00:02:21,320 --> 00:02:22,370
It's been reviewed.

33
00:02:22,370 --> 00:02:25,370
I was 8.0, 7.5, 8.5, 7.7.

34
00:02:25,370 --> 00:02:28,700
But there's a problem here, which is it's not doing what we want.

35
00:02:28,700 --> 00:02:31,820
We're not even we're not really close to what we want.

36
00:02:32,660 --> 00:02:36,800
In fact, we're ignoring the data we want because this is an inner join.

37
00:02:37,430 --> 00:02:43,490
So this is only finding the exact intersection between reviews and series.

38
00:02:43,910 --> 00:02:47,150
So wherever they overlap is what we're seeing.

39
00:02:47,150 --> 00:02:53,000
But we also want to identify where they don't overlap because that tells us that there's not a review

40
00:02:53,000 --> 00:02:54,020
for a series.

41
00:02:54,650 --> 00:02:57,230
So what we want to use is a left join.

42
00:02:57,860 --> 00:02:58,970
So hopefully that makes sense.

43
00:02:58,970 --> 00:02:59,600
Why?

44
00:02:59,840 --> 00:03:05,840
Because on the left, if we have our series right, we have all the series, some of which have matches,

45
00:03:05,840 --> 00:03:08,360
some of which don't, and we do a left join.

46
00:03:08,840 --> 00:03:14,150
It's going to take everything from the left, including the ones that have no reviews, and it's still

47
00:03:14,150 --> 00:03:17,960
going to stick them in the join table, but it's just going to fill their values with NULL.

48
00:03:17,960 --> 00:03:19,700
So we'll be able to identify that.

49
00:03:19,700 --> 00:03:21,650
So if we change it to left, join.

50
00:03:24,730 --> 00:03:26,260
And we scroll through some of this.

51
00:03:27,970 --> 00:03:31,540
You'll see somewhere we've got Archer with all these reviews over here.

52
00:03:33,960 --> 00:03:35,130
We keep going down.

53
00:03:35,160 --> 00:03:36,090
Here we go.

54
00:03:36,150 --> 00:03:37,170
Malcolm in the middle.

55
00:03:37,320 --> 00:03:40,800
Malcolm in the middle has no, no, no.

56
00:03:40,800 --> 00:03:42,860
Because there are no reviews to match it with.

57
00:03:42,870 --> 00:03:48,870
But because we did a left join, we still get Malcolm in the middle and pushing data which is also pushing

58
00:03:48,870 --> 00:03:53,210
data SGS Pushing Daisies which is no, no, no, no as well.

59
00:03:53,220 --> 00:03:59,970
So we can use that to our advantage because wherever we see null means that there are no reviews.

60
00:04:00,630 --> 00:04:03,870
So what we want to do first, let's whittle it down a little bit.

61
00:04:04,710 --> 00:04:13,760
What we're looking for at the end is just title, but let's start by just doing title and rating.

62
00:04:13,770 --> 00:04:14,610
Let's do that.

63
00:04:16,290 --> 00:04:18,000
So it's a little easier to see.

64
00:04:19,649 --> 00:04:24,870
So now what we want to do is only work with the data where there is no rating.

65
00:04:25,570 --> 00:04:27,820
So that's actually quite simple.

66
00:04:27,850 --> 00:04:35,290
What we need to do is use aware and then we can pick rating, for example, equals and we can say equals.

67
00:04:35,290 --> 00:04:37,840
No, because that's problematic.

68
00:04:37,840 --> 00:04:38,560
Remember?

69
00:04:38,800 --> 00:04:41,390
What we need to do is do is null.

70
00:04:41,410 --> 00:04:42,400
Just like that.

71
00:04:43,210 --> 00:04:44,770
So let's try this now.

72
00:04:45,400 --> 00:04:49,570
And you'll see we only end up with these two title and rating.

73
00:04:49,870 --> 00:04:54,250
And we don't have to do rating, by the way, because there are other fields that are null.

74
00:04:54,460 --> 00:05:00,520
If we go back to Star, there are quite a few things that are null.

75
00:05:01,600 --> 00:05:11,050
We could have done ID although this is refused ID, we could have done rating series ID or review ID

76
00:05:12,310 --> 00:05:16,070
or review or ID, but it doesn't matter which one we pick in this case.

77
00:05:16,090 --> 00:05:19,490
So let's go back and all we actually want to do is select the title.

78
00:05:19,510 --> 00:05:20,560
That's all we need here.

79
00:05:22,390 --> 00:05:23,290
Paste that in.

80
00:05:24,640 --> 00:05:26,700
Only thing that's missing is the alias.

81
00:05:26,710 --> 00:05:30,040
So we'll do select title as unrevealed series.

82
00:05:30,820 --> 00:05:33,040
Copy that in and it works.

83
00:05:34,060 --> 00:05:37,050
So the key thing here was that we used a left join.

84
00:05:37,060 --> 00:05:39,420
We wanted to highlight those discrepancies.

85
00:05:39,430 --> 00:05:45,730
This is the this is a use case for left join where you want to see where there is an overlap.

86
00:05:45,730 --> 00:05:50,560
So hopefully that that helps illustrate a bit more the difference between inner and left joins because

87
00:05:50,560 --> 00:05:54,910
if we had only done an inner join here, we wouldn't get the result we want.

88
00:05:54,910 --> 00:05:56,380
We wouldn't have any nulls.

89
00:05:56,380 --> 00:05:58,630
So this wouldn't select anything at all.