So how can we now query data
across multiple tables?
For this, let's maybe say what we wanna get
are a list of all the restaurants
with the address data mixed into it.
So that I have my restaurants here,
I'm querying from restaurants.
But now, instead of having just the address_id here,
I would rather have all the restaurant data
merged into the result.
It's stored in separate tables for a good reason,
so that the data pieces are very manageable in our database.
But when I work with that data,
when I fetch it into my application,
I might wanna get that merged view
where I have one large table
that doesn't actually exist in the database,
but which I get as a result
that has the full address data in it
instead of just the address_id.
Because otherwise, if we couldn't get such a merged table,
what we would have to do is select the restaurants
and then run extra queries for getting their address data
by that ID.
That would be extra steps,
which are unnecessary and which cost extra performance.
That's why instead in SQL,
we have a very important keyword or functionality
built into that SQL language
that allows us to actually query across multiple tables
and that allows us to join the data from these tables.
And that's the keyword JOIN.
What you can do when you run a select statement like this
is you can add INNER JOIN after the SELECT FROM part.
There are also other joins like left join,
but the INNER JOIN is the most common and important one
and therefore we'll focus on that here.
With the INNER JOIN,
you can combine data from different tables.
Now, it works such that after INNER JOIN,
you specify the table that has the related data
that you wanna join.
For example here, addresses,
so we wanna join addresses with the restaurants.
Now for INNER JOIN to work,
you now need to let SQL know
on which field you wanna join those tables.
So which field, which column in restaurants contains data
that can also be found in addresses that allows SQL
to then link records together.
And in our case, that's the address_id
because in the restaurants table,
the address_id stores ID values from the addresses table.
That's why we added that extra table field here.
So therefore now after specifying the related table name,
after INNER JOIN, we add the ON keyword
and now we let SQL know how to connect records
from the two tables.
And here, we would simply say that we wanna join
ON restaurants.address_id,
so on the address_id field in the restaurants table,
the dot is important,
where the value stored there is equal to addresses.id.
So we wanna join records where the values in address_id
in the restaurants table meet the values in the ID column
in the addresses table.
And if you now execute this statement,
possibly wrapping parentheses around this here,
but that's optional, then what you get back is this table
as you can see here.
And that's now not a table
that would be stored in a database.
Instead, it's basically just a result set
that is created on demand for this query
and that's returned to you.
And this now does not just contain the restaurant data
like the name, but also the complete address data.
And I'm selecting everything here.
Of course, you could now also be more restrictive.
Instead of selecting everything here,
you could also just select let's say restaurants.name
and then separated by a comma, addresses.star.
That would say that for the restaurants,
I only wanna get the name.
And for the addresses, I wanna get everything.
And maybe I also wanna get the restaurants.id though.
So that's now a query we could execute.
And if we do so, we get that data.
We don't get the address_id and the type_id anymore.
Instead, we now get the restaurant name
and the full address data for every restaurant.
And it is worth pointing out that now with that, of course,
we actually don't work with a table
that's stored like this in the database,
but as mentioned before, an on-demand created results set,
which we do create with this statement.
Now, you are also not limited to just one join.
Often, you wanna join data from more than one table,
and that is something you can do.
For this, I'll split this across multiple lines
just to make it easier to read
and I'll simply add another INNER JOIN here.
So we already have one INNER JOIN.
Now I'll add another INNER JOIN.
And here, I wanna join on my types as well
so that I also get all the type names for my restaurants.
Because in the restaurants table, we just stored a type_id.
Now I wanna get the human readable name instead.
For this, I INNER JOIN on types
and I join on restaurants.type_id being equal to types.id.
So basically what I also did for the addresses.
Now, I just wanna make sure that I also then print and show
the human readable type
and therefore here after addresses.star,
I'll make sure that I show types.name.
We have a name column in the types table,
and I wanna get that name once we joined the tables.
So now if we execute this,
you also have the name field here at the very end
and that's our type name.
Now, since we have two name fields here now
for the restaurant and for the type,
we can also assign a so-called alias with the AS keyword
which allows us to set a new column name here
in our results set
like this.
Now we have type_name here,
which is not the actual name of the column in the database,
but a name that is chosen here for this result set.
So that's now how you can join data from tables.
Now, we can also add WHERE clauses here.
Let's say we wanna get all the restaurants
that are in Munich.
Now for this, we can add a WHERE clause here
after the INNER JOINs.
And that's important. It has to come at the end here.
And there, we can say WHERE addresses.city
is equal to Munich, for example.
We can work with our addresses table here
because we're joining it into this overall SELECT statement.
And hence, if I execute this now,
I only get the restaurants in Munich.
So that also works.
And of course, there's more you can do with that.
I can only encourage you to play around with that
and experiment with that,
but as always and as mentioned before,
the official SQL documentation or a complete SQL course
like our course or any other course of course
is the way to go if you wanna learn all about SQL.
Now with that, we covered basically all the basics
I do wanna cover here though
and therefore to now conclude this,
here's a little challenge for you.
Write a query from the ground up
where you select all the reviews
where the rating is greater than three
and make sure you merge in all the restaurant data
and the full address data for these restaurants
and the full type data for these restaurants
into the result set.
That's a challenge for you.
We'll write and execute that query together
in the next lecture.