Okay.
Moving on to our next challenge with the same data.
Something a little new and frankly refreshing to see such a short table.
So what we're doing here is identifying the unrevealed series in our database.
So there are a couple that nobody has written reviews for.
For whatever reason, I haven't seen either of these shows really.
I've seen Malcolm in the Middle maybe once or twice as a kid, but I don't feel like I can review it.
And apparently nobody else in our app felt that way.
So we have two unrevealed shows, and the point is not to cheat and to just use this information to
find it, but to actually find it based off of the data we have in our database dynamically.
So how do we write?
It's going to be a join.
What type of join do we need to write where we can see what series have not been reviewed?
Well, let's give it a shot.
We'll hop over to Cloud nine and we'll start with our handy dandy comments.
We are on Challenge five and this one is an reviewed series.
Okay.
So what tables are we going to need to join here?
So we've got if we go all the way back here, we've got reviewers, we've got series and reviews, and
we don't need to care about reviewers.
We don't care about names of who is reviewing things.
What we care about is the overlap or lack of overlap between series, TV shows and reviews.
So let's start with a simple join select and we'll do star from series and we can just start with that
nice and simple.
It doesn't tell us anything.
We have no idea which ones have been reviewed or not, so we need to join it with the reviews.
So that's going to be join reviews.
On and it's going to be our typical join so series.
Dot ID equals reviews dot series ID and let's make this explicitly inner just so we're clear there and
when we do this we get a lot right.
We see Archer with an idea of one.
It's been reviewed.
I was 8.0, 7.5, 8.5, 7.7.
But there's a problem here, which is it's not doing what we want.
We're not even we're not really close to what we want.
In fact, we're ignoring the data we want because this is an inner join.
So this is only finding the exact intersection between reviews and series.
So wherever they overlap is what we're seeing.
But we also want to identify where they don't overlap because that tells us that there's not a review
for a series.
So what we want to use is a left join.
So hopefully that makes sense.
Why?
Because on the left, if we have our series right, we have all the series, some of which have matches,
some of which don't, and we do a left join.
It's going to take everything from the left, including the ones that have no reviews, and it's still
going to stick them in the join table, but it's just going to fill their values with NULL.
So we'll be able to identify that.
So if we change it to left, join.
And we scroll through some of this.
You'll see somewhere we've got Archer with all these reviews over here.
We keep going down.
Here we go.
Malcolm in the middle.
Malcolm in the middle has no, no, no.
Because there are no reviews to match it with.
But because we did a left join, we still get Malcolm in the middle and pushing data which is also pushing
data SGS Pushing Daisies which is no, no, no, no as well.
So we can use that to our advantage because wherever we see null means that there are no reviews.
So what we want to do first, let's whittle it down a little bit.
What we're looking for at the end is just title, but let's start by just doing title and rating.
Let's do that.
So it's a little easier to see.
So now what we want to do is only work with the data where there is no rating.
So that's actually quite simple.
What we need to do is use aware and then we can pick rating, for example, equals and we can say equals.
No, because that's problematic.
Remember?
What we need to do is do is null.
Just like that.
So let's try this now.
And you'll see we only end up with these two title and rating.
And we don't have to do rating, by the way, because there are other fields that are null.
If we go back to Star, there are quite a few things that are null.
We could have done ID although this is refused ID, we could have done rating series ID or review ID
or review or ID, but it doesn't matter which one we pick in this case.
So let's go back and all we actually want to do is select the title.
That's all we need here.
Paste that in.
Only thing that's missing is the alias.
So we'll do select title as unrevealed series.
Copy that in and it works.
So the key thing here was that we used a left join.
We wanted to highlight those discrepancies.
This is the this is a use case for left join where you want to see where there is an overlap.
So hopefully that that helps illustrate a bit more the difference between inner and left joins because
if we had only done an inner join here, we wouldn't get the result we want.
We wouldn't have any nulls.
So this wouldn't select anything at all.