Next up, we have another challenge, and this one is a little bit different.
I want us to identify the series that have no reviews.
So how are we going to do that?
Well, we need to use a join and it's not going to be an inner join.
We'll need to join the series with reviews and then see where there are series that have no reviews.
Where some value is null.
So we'll do something like select.
Let's just start with Star from series and then we'll do a join reviews, the reviews table, but that's
going to be an inner join.
We'll start with that and we'll say join on where the series ID is equal to the reviews dot series.
I'd.
If we do this, this only gives us the exact overlap.
Only the series that have a review are joined with the review.
But what I want is actually a left join in my case because I want all of the series information and
then any corresponding reviews information.
So if I change this to be a left join, otherwise keep it the same.
Now we see something a little different.
It looks all the same until we get right here.
These two series have no corresponding reviews, so we get.
No, no, no, no.
So now all we need to do is only select the ones that have no there.
We're trying to get the title right.
Malcolm in the Middle, Pushing Daisies.
That's all I selected.
I don't want anything else.
So I would do something like select from this table where we could say where rating is null, where
series IDs review or ID, anything, where one of these is null, and I think I'll just do where rating
is null.
So where rating and I can't do equals remember with null we use is null.
And now I've narrowed it down to these two rows where rating?
I just picked one column.
But where rating is No, that would indicate there was no matching reviews.
And then I just need to select the title.
And that should do it.
I think I gave it an alias of unrevealed series, so let's add that in.
As an reviewed series.
Okay.
So another situation where left join helps us, right?
It reveals information or the lack of review for a given show that we wouldn't get when we use a inner
join, which only gives us the overlap.
Now I could rewrite this as a write join really quickly, just show that same thing.
But instead of starting with series we would select.
We could still do title as on reviewed series, but we'll select to start with from reviews.
And then do a right join, because the right side will be the series table and then we'll do on series
date ID equals reviews series ID where rating is null.
And this should be equivalent because now we have series on the right side and we're doing a right join
here.
We had series on the left side.
We did a left join and we get the same answers.
Malcolm in the Middle, Pushing Daisies.
Next up, another challenge.