OK so in the last video we saw our first join our first example which is actually something known as
an implicit join which you'll see for a moment where it's called implicit and it's also in particular
it is a CROSS JOIN useless basically.
So it just think of it as multiplying our data across multiplying it takes every possible combination.
So we have Boy George Well I don't need to go over that again.
Bore bored to death.
So now we want to focus on whittling it down to what's actually relevant.
I want to see the name of the name first name my surname and email of every user who actually placed
an order next to that order not just every arbitrary order I want the ones they placed.
And so we're getting sort of close if we have all of it next to each other.
Now we just want to only select the rows where what what condition is true where our user ID is equal
to the order.
Customer ID.
So it's just like any other kind of select we've done where we just can add in a where.
So it will be a select star from customers comma borders where and where what is true where ID from
the customer is equal to customer ID.
So we could do this where a customer equals customer underscore ID.
But there's a problem and maybe you've identified it.
We have two IDs remember we're just taking the customers table in the Orders table and putting them
together.
So we have I.T. from customers and ID from orders.
So what does this refer to.
That's a problem.
Fortunately there's a workaround.
It's very very simple.
All we have to do is prepared the name at the tables.
So this should be our customers idea right.
It's coming from here.
And just to make that clear what's happening we're saying where the customers table Id So where this
is equal to customer ID.
Now customer underscore ID and I realize there's a lot of saying customer and Id over and over but these
are different.
Right.
This is the customers table ID that's referring to this customers dot.
That dot means in the Customers table and this is customer underscore ID which is referring to this
right here.
So we dont have to say orders dot customer ID but its a good convention to follow to do that just to
whenever you're working with two or more tables to be explicit about which table were which row.
Richard excuse me which column belongs to which table.
OK.
So we have this line.
Let's copy it over.
So what this will do is select it's going to do this cross join but only where the customer's ID customer
table ID is equal to the customer ID of the order.
So now if we do this take a look at what we get.
We have five orders.
So we get these five different rows and we have a corresponding customer who placed it.
So Boy George placed this order for $99.
Boy George placed another order for $35.
George Michael placed these two orders and Bette Davis placed this one order for $450.
So you can see we have customer id over here and its matching the customer's ID and of course we could
condense this a bit to maybe we dont want to start maybe we want first name and last name and then order
date and amount and that's it.
Just like that.
So let's try it and now we get a more condensed table that has had information Boy George or George
tourers George Michael one order George Michael another order Bette Davis another order.
So what we've just done there we have joined them together using what's known as an implicit inner join.
So I'll write that here.
OK.
And that's what we did appear as well.
So this is just a more refined one.
Kids we're not selecting star we're only citing a couple of things but we've joined them where they
match if that makes sense.
So I have a simple diagram it's very very ugly but a simple diagram to show you and I'm sorry it's so
ugly.
It was very difficult.
It's filling in this shape.
It's not a circle and the slides tool I used doesn't let you draw weird shapes you have to do circles.
So I'm sorry I really am.
But what's happening here is that we have two tables right customers and orders and what we did at first
was select everything.
Combine them and just take everything together.
But all that we want to do with an inner join is take it inside where they overlap and that's what we
did.
We went from this monstrosity and we will down to where the customer's ID is equal to customer ID in
the orders table and we get this right here.
OK.
Now I said this is implicit.
And that's because there is an actual explicit syntax.
There's a better way of doing this.
And when I say better it really means that it is more conventional If you're not familiar with that
it means it is they give it as like an unwritten rule that developers follow.
They think it's cleaner it's more easily understood to do it this way which I'm about to show you.
So I'm going to write that I do.
Here you go.
And when I write a comment explicit inner join and this will be the first time we see a new keyword
called join.
So we're going to do our same select star.
We'll start with this one from customers.
However we're not just going to do come orders we're going to start from customers and then we're going
to add joyn orders.
So we have some new syntax I'm just going to type it all first and then we're going to tell it where
we're going to join on customers thought Id equals orders dot customer ID.
OK.
So if I copy this and I run this it does the exact same thing.
And if we want to of course we could refine this more just like we did earlier so instead of selecting
star let's do the same thing or we select first name last name order date and amount.
OK.
Copy this over and now we get this the same table in just to prove to you that it's the same.
The ego can even see the difference when I.
I just hit enter and it looks like the same thing is here.
OK.
So this is still an inner join.
But this time it's explicit and it's really only explicit because we're writing the word join.
So when we say join order so we say from customers join orders on this join condition.
So take the left and the right to which customers and orders these two circles basically enjoying them
create the Union table where customer ID is equal to orders customer ID.
So where the ID from here matches the customer ID from here take that overlap and make this table for
us.
So I know it's a lot and I'm talking about here.
Now the term inner join an explicit inner join implicit inner join.
And we're going to next I'm talking about right joints left.
It's can be overwhelming.
So I want you to focus on is the key idea here that we are joining data together based off of a condition.
Right.
And we could join that off of some meaningless condition to just to show you.
So if we go back to this this really ugly cross join where it takes everything and joins it together.
So if I wanted to there's no meaning to be garnered from this but I could join it where the two ideas
are the same or not customer ID but I can say where customers.
Id is the same as orders that ID.
So let me just show you that now.
And I'll just comment this as arbitrary join.
Don't do this but we could do this we could select star from customers join orders on not to say we're
customers ID equals orders.
Heidi there's nothing stopping me from doing this.
And as you can see I just end up with this new table that has a matching IDs so we get.
Let's take an example like George Michael.
So George Michael is now matched he has an idea of two he's matched with the order of I.D. two.
It doesn't matter that it was placed by customer with ID of one which is boy George.
So I'm saying that we can just join things on arbitrary conditions.
However you're typically what goes in here is you're filling in a foreign key.
Matching is a primary key in another table.
So primary key is a customer's I.D. a foreign key is the customer underscore I.D. in the Orders table
and then the last thing that I'll wrap up with here is does the order matter.
So if we go back to our cross join.
What if I reverse order so I started with customers comp orders and so we get customers here and then
orders tacked on on the right.
So what if I switch it orders Khama customers and you can see well it looks very similar except it doesn't
look similar we had the same data but it's been switched.
So we start with every order first and then one customer tacked onto the end and then every order again
and then the same customer.
So the order does matter in a sense as to how your data is presented to you but it doesn't matter in
the in the case of a cross join or in the case of an inner join it won't actually affect the result.
It just affects what is printed out what it looks like.
So let me show you if we take this same We've got a lot of them going on here.
Let's take the same one here.
This is kind of the most useful join we had right where we had the name and the order date and the amount.
If I switch I'm just going to duplicate it so that we have the original Oswell.
If I say from orders join customers on and we can leave this either way.
But let's say do that.
So I'm selecting from orders with joining customers onto it.
And I hit enter.
Nothing changes and that's because we are picking our data you know individually from a peer.
Now if I just did a star
it will look a little different because you can see we're actually getting order first so order ID order
date amount and then customer.
And if I switch that
it would be the other way around.
That doesn't really matter.
It's just orders but you can see now we get customers and then orders.
All right.
So this has been long and a little difficult I imagine especially some of the syntax or keeping that
jargon.
What I do want to say is that basically when you're joining things don't do implicit joins which is
what we have here.
It's better to do it explicitly this way.
So something like this or this where we have select something from a table join on another table on
a condition OK.
So that is kind of the bread and butter of joints doing an inner join.
But I'm going to show you a couple of other examples that will be up next.