All right.
So there are three main types of joints we're going to cover.
Ignoring the cross joint we briefly saw we're going to see inner joints, left joints and right joints.
And this stuff can seem a little intimidating when you're starting out.
But I hope by the end of this video you'll feel more comfortable, at least with inner joints.
So I have a diagram here.
It's not particularly attractive.
It's hard to make these this overlapping area in the middle using my slide software.
So it's not perfect.
But what we have are two circles representing our two tables, and the middle is the overlap.
That is what we would target using an inner join.
So again, when we see a left join, which is coming up later, it's slightly different, right?
We have the middle and the left where we have a right joint.
It's the middle and the right, but we're not there.
We're talking about inner joints only and this is the most intuitive join.
If we think about our customers and our orders, where if I go back and we select star from customers
and we select star from orders, we know that we have this customer ID 112253.
And if I was looking at this as a human and I wanted to figure out who placed this order, well, I
would look at this customer ID, then I'd come over here and find the customer with that ID right there,
which is George Michael.
And then I see.
All right, George Michael placed this order.
George Michael placed this order.
We found the overlap.
Right where the customer ID in this row matches the actual customer's ID in this table.
And then we look at the first name or the last name so we can write a join to do that for us.
We'll write a join that says select.
I don't know, first name and last name alongside the order date in the amount, but only do this where
the customer ID from the customer's table matches the ID from the orders table.
So I'm going to write this index out first.
Actually have a slide to show it.
It looks like this select whatever we want from customers, but then we get some new syntax join and
on.
So let's just start with join.
We have to specify what table we're joining customers with.
So if we start with customers, we'll join orders.
We also could go the other way around, but we'll start with customers join orders and then we tell
it how to do the joining.
We want to join on where the customer's ID is equal to the orders Table DOT, Customer ID, let's just
type it out, start from scratch, so we'll select star from customers if that's what we start with.
And then we add our join and the table that we're trying to join, which is orders.
So we're halfway there.
Now we have to add in the en portion saying how to do the actual joining.
And we want to perform the join that I kind of described using my mouse where the customer ID from the
orders table matches, the ID from the customer's table.
And the way we do that is by referencing we can't just say ID equals ID or ID equals customer ID, we
have to tell my SQL which table the ID comes from because now we have two tables.
They both have an ID, right orders ID, customers ID, so we're going to say join on where the orders
DOT customer ID column is equal to the customer's dot ID column.
So where the customer ID column is equal to the customer's table ID column and that should do it for
us.
I'll run this query.
And we said select star.
Right.
So we get everything, both IDs, we get first name, last name, email.
So this on the left hand side ID, first name, last name, email is all from the customers table.
On the right hand side, we have ID order date amount, customer ID that is all coming from the orders
table.
Now here's the most important part.
Take a look at the ID for the customer and the customer ID they both match.
So we are seeing who placed each order and what their email is and what their ID is alongside each order,
date and order amount.
But we might want to slim it down.
This is more information than we probably want.
We could say select first name, last name, and then maybe what is it?
Date Order.
Underscore.
Date.
And amount.
Maybe those are the four things we might be most interested in.
There we are.
So we can see.
All right.
Boy, George ordered on whatever this is.
February 10th, 2016, $99.99.
He also ordered 2017 3550.
So we just slimmed down the columns that we're selecting.
But the joining is still happening.
We just aren't looking at the actual IDs anymore.
But it's still all happening behind the scenes.
Now, what I want to show is we can also go the other direction.
So we started with customers and we joined orders.
I could have said select.
Let's just do Starr from orders join customers on and then the order of this part doesn't matter.
I could say customer's dot ID is equal to orders.
DOT customer ID.
And if I run this.
We see the same information, but the order versus what's on left versus what's on right is different,
Right?
We see the order information first and then we see first name, last name, email.
But it's the same information.
We have six rows.
So all that changed is the order that things are printed out, the order of those columns, which is
confusing because we also have a table called orders.
But I'm talking about this sequence.
Let's call it the sequence.
The sequence is different ID order, date amount versus here we started with ID, first name, last
name, email, but the information is the same so we can join either way.
But what matters the most is we use this join keyword to bring in information from another table and
we have to specify how to connect the to what are the arrows going to be that are connecting one table
to the other.
And the answer is using the on clause we specify from the customer's table, take ID and whatever.
That's the same as the orders table.
Customer ID, We have a match now this is a useful join.
I could just do whatever arbitrary joint I wanted to.
I could do this.
Select everything from orders where the customer ID, meaning the ID from the customer's table is the
same as the ID from the orders table.
Why would we do that?
It doesn't make sense.
There's no meaning to be garnered from that.
But I'll just show you that there's nothing stopping us from it.
So now what we've done is selected all of the rows from order where the corresponding row from customer
has the same ID.
So this customer, Boy George has ID of one, This order has ID of one.
This customer, Bette Davis, has ID of five.
Well, she's matched up with this one that has ID of five this order.
It doesn't make sense to do that, but we can do it.
Generally though, we would actually make a join that told us something that connected information that
is not arbitrarily connected by the order of their IDs, but rather by a foreign key.
And remember that this is an inner join.
We are getting the overlap between customers and orders or orders and customers.
Technically, the inner here is implicit, but I can write it out if I wanted to explicitly use an inner
join.
It's just the default.
If you say join is going to be an inner join.
But once we learn left and right, we have to specify left and right because those are not the default.
So even though we're not writing inner, it is truly an inner join.
So I'm going to get rid of this arbitrary join that makes no sense and leave you with this one here.
That is something we would actually do, although more often than not, I would narrow down what I'm
trying to select because when you join two tables together, these are very small tables.
They have four columns or three columns each.
It already adds up to quite a lot of information, but with real complicated tables, we might have
dozens and dozens.
So when we join them together, we often will slim down what we select something like this.
All right.
So that's our first example of a join.
We have more to cover.