-: Hi guys, and welcome back.
In this video we're going to learn
how to extract data from two
or more tables at once using JOIN.
So let's say we've got one table here for users
and the other one for accounts.
On the left you can see you've got the id column
and on the right you've got id columns and holder id,
which as you know,
are primary and foreign keys respectively.
Using JOIN, we can make a query like this one.
So here we've got,
select *
from,
but then we're not only putting one table here,
we're seeing users JOIN accounts.
And this is going to essentially join both tables together
and allow us to perform a select or where closes
and order buys and so forth from the joining of both tables.
Important we have to say how the tables are joined.
And so here we are saying that for each row
on the left table,
we're taking the id value and we're mapping it to
the holder_id value on the right table.
So here is the final table,
how it's going to be constructed.
We've got all the columns from both tables.
That's what the select * is telling us.
And notice that that means that we've got id first name
surname from the users table,
and we also have id holder_id,
a number from the accounts table.
There's two id columns there,
that's totally fine,
we know that they mean different things.
Now then we're going to map the id values on the left
to the holder_id values on the right,
that's what the own clause does in our query.
And when that happens,
we're going to extract both rows,
join them together,
and that's the result of our JOIN.
So the same thing's gonna happen
for every other row here.
We've got row two,
you can see that the id value on the left
always maps to the correct holder_id value on the right,
and that's how our table gets constructed.
Now, instead of select * to select all columns,
we can select only certain columns.
Here for example,
we've got users.*
that gives us all the columns from the users table,
and accounts.number that gives us the account number only.
So the end result of this query would be four columns,
the three from the users table and the account number.
Notice that the JOIN is still the same,
it is not affected by what we're selecting,
so we can still compare on accounts.holder_id,
even though we're not selecting that totally fine.
The order of the JOIN sometimes matters,
so accounts JOIN users is not the same as
users JOIN accounts,
the table on the left,
and therefore the columns on the left
are gonna be different.
But there's also a few more differences
that we're going to learn about when we learn about
the different types of JOIN in a couple of lectures time.
For now though, you can see that the table on the left
is going to have all its rows there
and we're going to map to it the table on the right.
So can you only join the primary and foreign keys?
No, you can join on any column,
but using primary and foreign keys is gonna be much faster.
So that's normally what you'd wanna be doing.
Thank you guys for watching,
I hope you've enjoyed this video,
I'll see you in the next one.