All right.
So we're going to learn our second type of join, which is the left join.
I'd say this is nowhere near as commonly used as an inner join, but it's still important to know about.
And the good news is, once you understand the left join, it makes understanding right joints very
easy.
So if you take a look at my ugly diagram, I've got two circles representing two tables and presumably
they have something in common.
There's some foreign key.
And where we had an inner join which only selected the overlapping rows where there was a match between
customers and orders here with a left join.
If we have customers as our left table and orders as our right table, we are going to take every single
row from the left side, every row from customers, and then if there's any corresponding order, data
will also join that.
But if not, we'll have a bunch of nulls and I'll show you what that looks like.
So keep this visual in mind.
Everything from the left side and then any overlap from the right side.
So let's do another join here.
Select.
Let's just do first name.
Last name.
Order, date and amount from customers.
And when I say from customers, that now is going to be the left side, just like here, customers on
the left.
If I write customers first, then I join orders that would be on the right.
So I'll do select all that stuff from customers and then instead of just join or inter join, which
is equivalent, I'm going to say left join.
And then it's the same thing.
I have to specify the table, the orders table on, and then I'm going to do the same exact thing.
This hasn't changed.
I still want to join where the orders table customer ID matches the actual ID from the customer's table.
I need that overlap there, regardless of the type of joint I'm doing.
The thing I'm joining on in our table is not going to change here.
We have one foreign key and we want to match it up with the ID from customers.
So anyway left join orders on and then we can do orders DOT customer ID wherever that customer ID from
the orders table matches customer's ID, So put a semicolon there.
When I execute this, you'll see that we get a different result if I just go back to plain old join
in or join.
Or just join.
We see we've got, what, five rows here versus seven rows here.
So on both of these examples, we have a left side and a right side.
The left side is customers.
The right side is orders.
But remember this difference in an inner join, we only get the overlap in a left join.
We take every single row from the left side.
So this is our left join.
Every customer is represented here.
Boy George, George Michael, David Bowie, Blue Steel and Bette Davis.
And then if they happen to have any overlap from the right side from orders, then we see that information.
Boy George placed an order here.
Boy George had this order.
George.
Michael, This order.
George.
Michael, Also this order.
But then David Bowie has no orders in our database.
Blue Steel has no orders in our database.
So it's a left join.
We get those names, we get the information from customers.
There's just no corresponding information from orders, so we get null values instead.
Now, if we compare that to the inner join, we only get that intersection where there is an overlap,
a left join.
We take all information from customers.
Every row from customers is here and any corresponding order information is filled in.
But if there isn't any, we just get nulls.
So that's a left join.
Why would we do this?
What is the purpose of a left join compared to an inner join?
Well, sometimes this can reveal important information.
If I wanted to know do we have any users who have never placed an order who are completely inactive?
This would be one way to find that out.
Really, the main way to find it out an inner join is not going to help me.
This only shows users who have placed orders with this.
If I only looked at customers, that's not going to help me either.
I keep saying users, but I mean customers.
If we select star from customers, that tells me nothing about who's placed in order.
So really the only way to find out is to overlap customers or join customers with orders and see where
there is no corresponding order for a customer.
So that shows us David Bowie and Blue Steel have no orders.
So then I could do something like calculate how many nulls there are or Group VI things.
There's other stuff that I could do, but it's just important that, you know, there are real use cases
for this.
There are times we want to know where there is no match up from the right side.
Not always.
Often inner joints are more useful, but that doesn't mean left joints have no purpose at all.
They come up and again, going back to this diagram, we get every single row from the left table and
then the right table will fill in any places where there's a match, where there's overlap.
Now, one thing that's a little confusing if I switch the order here, so if I said select from orders,
that would be my left side.
Join customers and then I'm going to reorder these properties or these columns so that it looks left
to right.
So the order doesn't technically matter here.
This is what makes a left and this makes the right.
But I've put these on the left, the order columns, and I've put the customer columns in the right.
But my point is, if I run this.
We get the same thing as an inner join.
It's the same result that we got when we did our inner join right here.
Why is that?
I mean, excluding the fact that I have order, date and amount first, it's the same matches.
Well, if you think of our data and how it works, if we select star from orders, we don't have any
orders that don't have a customer ID, everything from customer ID.
If this is our starting point, this is our left side, and then we match anything from customers.
Well, they all have a match.
There's a complete overlap.
So the left side is the same in this case as the middle.
So the end result is the same as our inner join.
But if we had any orders that did not have a customer ID, then the end result would be different.
But in this case it's not.
So that's a little confusing.
It's just the way that our data is set up, and that's common with one to many.
We'll have customers who don't have orders, but we definitely won't have orders that have no customer
because how would that make sense?
All right.
I hope you're hanging in there.
It's a lot.