- [Maximilian] So let's now apply what we learned
and let's write a brand new query.
We wanna select all our reviews from the reviews table,
where the rating is actually greater than three.
So where rating is greater than three
now if execute this, I get all these ratings
but now I wanna merge in the related to restaurant data,
and for this, we got this restaurant ID field, of course.
So therefore we need an INNER JOIN.
So before we add where we can add INNER JOIN
and we can join our restaurants in this case here
to our reviews on reviews dot restaurant underscore ID
being equal to restaurants.id.
With that, we tell SQL that we wanna merge
in our restaurants data matching on that restaurant ID field
and the ID primary key in the restaurants table.
And we still filter for the rating greater than three.
If I execute this, I got this result here,
which isn't too bad.
Now we have our restaurant data here,
but of course the restaurant data also does have some data
that can be merged in the address and the type.
So of course we can add more INNER JOINs here.
We cannot also join on the addresses
and we wanna make sure that we merge in the addresses,
matching on our restaurants dot address underscore ID
being equal to address dot ID.
So now we're joining on another table
that's brought in by our first join,
we are joining on our restaurants table
and the addresses table.
And that's also something we can do.
If I execute this, we got that address data in here.
Hence we can of course do the same for our types.
We can add another INNER JOIN
and showing the types on restaurants, whoops,
restaurants dot type underscore ID
being equal to types dot ID.
So what we did before as well now here in this big select
and join statement on our reviews.
And this now also works
now to clean up that output a little bit,
I don't wanna select everything though.
Instead here from my reviews,
I do wanna get everything let's say,
but for my restaurants,
I only wanna get the name as restaurant name
to make it clear that this is the restaurant name,
to give this column a clearer label.
I don't care about the restaurant ID
and I don't wanna get the address ID or type ID here
because I already got all that joined detailed data.
Now for the address, I wanna get everything
and for the type,
I only wanna get the name as restaurant,
whoops restaurant under score type, maybe.
So give that an extra label.
And if we now execute this, that's the results at now
and that's a bit more readable, I would argue.
We could remove more columns.
We could fine-tune this select statement even more
but with that,
you already see the most important thing in action already,
how you can write more complex select statements
where you control which kind of data you wanna select,
where you have a where clause and most importantly,
where you join in relate to data from other tables
with that INNER JOIN statement.
And this is super important because SQL
and working with SQL databases
is all about splitting your data across multiple tables
so that the data is easier to manage.
And you have that clear separation of data pieces,
but you then of course also need to be able
to merge that data together
whenever you need to work with it
whenever you need to query it.
And that's what you can do with INNER JOIN
in your select statements, as you'll learn it here.
And therefore,
this is now the essence of SQL and of working with SQL.
These are features, and this is a syntax
you will see a lot and therefore
it belongs into any web development course
and now you get these basics here as well.