Okay.
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 why 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 cross multiplying.
It takes every possible combination so we have.
Boy George Well, I don't need to go over that again.
I'll bore you 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, last name and email of every user who actually placed
an order next to that order, not just every arbitrary order I want the ones that 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 aware.
So it will be a select star from customers comma orders, where and where.
What is true?
Where ID from the customer is equal to customer ID so we could do this where 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 and the orders table and putting them together.
So we have ID 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 pretend the name of the tables.
So this should be our customer's ID, right?
It's coming from here.
And just to make that clear what's happening, we're saying where the customers table dot ID so we're
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 customer's table ID that's referring to this customer's ID that dot means in the customer's
table and this is customer underscore ID, which is referring to this right here.
So we don't have to say orders dot customer ID, but it's 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 with which
row excuse me, which column belongs to which table.
Okay.
So we have this line.
Let's copy it over.
So what this will do is select or 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 the corresponding customer who
placed it.
So Boy George placed this order for $99.
Boy George placed another order for $35.
George Michael plays these two orders and Bette Davis placed this one order for $150.
So you can see we have customer ID over here and it's matching the customer's ID.
And of course, we could condense this a bit, so maybe we don't want Star, 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 that information.
Boy George.
Two orders.
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.
Okay.
And that's what we did up here as well.
So this is just a more refined one because we're not selecting Star, we're only selecting 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.
Filling in this shape here is not a circle.
And the slide tool I use 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 the 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.
Okay.
Now, I said this is implicit and that's because there's an actual explicit syntax.
There's a better way of doing this.
And when I say better, it really means that it's more conventional.
If you're not familiar with that, it means that it's think of 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.
Oh, what did I do?
Here we go.
I'm going to 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 common orders.
We're going to select start from customers and then we're going to add join 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.
Dot ID equals orders.
Dot Customer ID.
Okay.
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 where we select first name, last name, order,
date and amount.
Okay.
Copy this.
Over.
And now we get this the same table just to prove to you that it's the same.
There you go.
Can't even see a difference when I just hit enter and it looks like the same thing is here.
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.
So which customers in orders these two circles basically and join them.
Create the union table where customers 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 that I'm talking about here and the term inner join and explicit inner join, implicit
inner join.
And we're going to next talking about right joints and left join it's can't be overwhelming.
So all 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 it 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 IDs are the same.
So not customer ID, but I could say where customers ID is the same as orders 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 and I'll just say we're customers ID equals orders
ID.
There's nothing stopping me from doing this.
And as you can see, I just end up with this new table that has 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 is matched with the order of ID 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 a primary
key in another table.
So primary key is a customer's ID, the foreign key is the customer underscore ID 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 the order?
So I started with customers, comma, orders, and so we get customers here and then orders tacked on
on the right.
So what if I switch it?
Orders to customers.
And you can see well, it looks very similar except well, it doesn't look similar.
We have the same data, but it's been switched.
So we start with every order first and then one customer tacked on to 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 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 this 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 and I'm just going to duplicate it so that we have the original as well.
If I say from orders, join customers.
On and we can leave this either way.
But let's say I 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 individually from up here.
Now, if I just did a star.
It would 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.
Oh, 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
the jargon.
What I do want to say is that basically when you're joining, things don't do implicit points, 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.
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.