I hope you're still with us.
I really hope it didn't put you to sleep with the last video.
These are getting a little too long for my taste.
Significantly too long.
So I apologize for that.
But there's a lot to cover here and there's no great place to break it up.
However, we are done with kind of the long slog of explaining joints.
Now I'm just going to show you another type.
So inner join is what most people end up doing.
That's a big generalization, but it's what I end up doing most of the time.
But there are other types, and the next one that I'll show you is called the Left Joint.
There's left and right.
They work very similarly.
So once you understand how one works, it makes it easier to understand the other.
So what we saw so far was the inner join, where we take one table A and another table B and then we
select all the records from A and B where the joint condition is met.
But there's another type of joint.
Before I get there, I want to show one other thing.
If we go back to any of our joints that we had, let's take this one here.
So this gives us a nice little table with Boy George and his orders.
We have George Michael and his orders and Bette Davis in her orders.
I can do just any regular MySQL functions and things that you're used to with this data.
So I didn't make that very clear in the last video.
This isn't some special table.
We can do whatever we want.
So I could do something like group buy or let's start even simpler.
I could do an order by let's do an order by and let me actually move this.
So we have a record of everything we've done, so I'll scroll down.
Getting fancier.
Here we go.
So we can do an order by.
The amount that if we wanted to.
And now we have from the lowest order up to the highest order or we could order by order date.
So that's one thing we can do.
But we can also do things like group by.
So maybe let's say this is something we actually might want to do is take all of our orders and group
them by who ordered them, group them by who ordered them and then take that and find the average amount
they spent or the total amount.
How about we start there?
Let's identify the biggest spender in our entire database, which it's pretty easy to do if you just
look here and add things up.
But let's say we had 10,000 records.
How do we find the biggest spender?
So we'll group buy and how do we group them?
What we want a group by our customer who place the order.
First name could work, but that assumes that we only have one Bettie or one George, and we might have
George Michael and George Foreman or something.
So that's not going to work.
We could do first name, comma, last name, but we still could have people with the same name.
So it's better to just group buy something we know is unique, like customer ID just like that.
And let's be explicit orders customer ID, so group buy that.
And if we hit enter, remember when we do group buy and we don't actually do any math or any functions
on it, it looks like it just cut data out because that's just how it prints things to us.
But now you can see that we have grouped by the customer ID, so I know it's hard to see because we're
not displaying customer ID, but remember that customer ID is unique relative to every user.
So we're basically grouping by customer.
Hopefully that makes sense.
You could make it a bit clearer if you did something like this.
What we've done and let me get rid of the group by.
Briefly.
What we've done is take these customer IDs here that we weren't showing and just grouping these two
together, these two together, and this one on its own.
But we're not actually displaying that.
So we can group by that like we did.
And then all we want to do is total together the amounts.
So rather than just amount, let's do some amount and I'm going to clean this up a little bit.
And let's give it an alias as total spent.
Just like that.
Now.
Let's try it.
Cool so we can see Boy George spent 135, George Michael 813 and Bette Davis 450.
And then lastly, we could order by total spent move our semicolon.
Now you can see and actually let's do descending.
Now you can see George Michael is definitely our biggest spender.
813.17 cents.
Perfect.
Now, order date doesn't really make sense to have in there.
So because we have multiple orders and it doesn't make sense just to show one.
So maybe this makes more sense.
There we go.
So I just wanted to show that you can still do typical things that we're used to with the results of
a join query.
It's not some special alien type of a table.
Now that brings us on to the left, join.
So another way of joining our data.
Here's the ugly diagram.
I tried to make it a little better.
The colors are still awful.
I'm sorry.
So what does it do?
Well, it's going to take everything from the first table.
So if customers is our table on the left, it will take every customer.
And then on the right, it will try and match.
Or it will match any or it will add any matching records from orders in our case.
So these diagrams, just talking about it like this, I find, isn't that helpful.
But basically think of it as it takes the union of where they match just like inter.
But then it also does everything from the left table, from customers in our case.
So let's test it out over in cloud nine.
I have a comment here for left join and let's go take one of our joints that we did earlier.
Like let's do this first one.
And this was an inner join.
And one thing I didn't mention is you can explicitly write in or join and I'll show you it does the
exact same thing if you leave it off.
It's implied that it's going to be an inner join.
It's the most common type.
What it does is we know it takes customers where the ID matches customer ID and that's all we get.
That's the inner part that matches.
Now, if we change this to be left and select everything, you'll see we get a couple of differences.
So we still have to kind of ugly to look at.
I'm sorry.
We have Boy George in the order he placed in Boy George, another order, he placed George Michael to
orders that match.
But then we have two users who never placed orders.
David Bowie and Blue Steel do not have orders.
And so we end up with David Bowie.
Blue Steel.
No, no, no, no.
For the orders portion and then Bette Davis and the order that she placed.
So we have customers on the left, orders on the right.
We're taking everything from customers.
So that's why we end up with David Bowie and Blue Steel, and then we take the intersection as well
where there is an applicable match.
So for David Bowie and Blue Steel, there isn't one, and it fills in null.
But for Boy George and George Michael and Betty Davis, there is a matching order.
So I know it's kind of difficult to look at and understand, but think of it again.
We have customers and we go through at the beginning.
Boy George, is there a matching order?
Yes, this one here.
Right here.
And this one.
So put those together.
George Michael ID of two.
Is there any matching orders?
Yes, there's two.
Put those two together next to George Michael.
David Bowie.
Is there a matching order?
No.
So just put David Bowie because it's a left join.
If it was an inner join, then David Bowie and Blue still don't show up.
But it's a left join, which means we're taking everything from the left table and it works perfectly
well, depending on what you're trying to do.
So that brings us to the question why would you do this?
And before we get there, I'm going to just clean this one up again so that we can see it a little bit
easier.
Let's only select first name, last name, order, date and amount, just like before.
So that brings us again to the question why would you do this?
Why would you want to have excess information like David Bowie and Blue Steel?
And it depends on what you're doing.
And in this case, maybe I previously I wanted to see all the orders that have been placed and names
next to them.
But now what if I want to tabulate the high spenders on our site, but I want to include people who
haven't spent anything.
So I just want to be able to see for every user how much have they spent.
And I'd be able to go through and say, maybe send an email to the people who have spent a lot.
Och, thanks for being a loyal customer versus people who haven't spent.
I could go through and send an email like Here's a coupon for your first purchase, 10% off or something
like that.
Right?
Where I want to basically get some insight where it involves knowing everything about all users and
some of them have corresponding orders and some of them don't.
Now that brings us to another topic, which is okay, but we have nulls here.
So.
Is there a way to change that?
Let's say I wanted to do the same thing that we had up here where we had our high spenders.
Right?
So we selected and we summed all the orders based off of grouping them by customer ID.
So it's a lot again, but let's do that here with a left join.
And there's going to be a slight difference, right?
Because we have nulls.
So if we do this select first name, last name, order, date and amount from customers left, join
orders and we do a group by customer state ID this time.
So that will group by customers so that we have these two grouped these two grouped on its own, on
its own, on her own and actually blue as a girl.
I'm sorry, blue OC So if we do this we won't see much difference.
But we will see a difference.
But it's not very meaningful.
What am I missing?
Extra semicolon.
Not that useful because it just condenses our data and hides stuff.
But if we go through and I'll format this again.
But some together.
Everything, right?
So will some.
And inside of here, we want to some amount and we don't actually need to have a mount there and we
can get rid of order date too.
So let's just have the first last name and then some.
Okay.
So this is accurate.
That's accurate.
We still have Noel here, which it can work, but it would be nice if instead it just said zero because
they've spent $0.
So how do we do that?
And this is not an integer.
This is not a join specific thing in any way.
This is just an interesting problem.
How do we get instead of null?
How do I replace that with zero?
And there's a couple of ways.
The first really lengthy, awful way is to use a case statement so I could have a case statement where
I checked is some amount is null.
Remember, I can't just say equals null, but if I said it's is null, then do zero.
Otherwise do some amount.
But that's that's kind of long.
So it's going to be much easier if I use what's known as if NULL And I haven't shown this to you yet
because we didn't really have many cases where we had null data.
But here's one.
So what if NULL does?
It takes two arguments.
The first one is the thing you want it to check if it is null or not.
So we want to check is sum of amount null.
So here it's not right?
It's not.
No, it's not null.
Here it is.
So then the second argument is what we want to replace it with, if it is null and we'll just say zero.
So it's very simple.
It checks if the first argument is null, if it is, it replaces it with the second argument.
If it's not, it leaves it as the first.
So now if I try it perfect, we get zero.
We should definitely add an alias.
Let's call it total spent again.
One other thing that would be nice is to order by total spent.
And this time let's do it from least.
So we'll just do order by total spent and that should work.
There we go.
And now you can see we have zero.
So we kind of talked about a lot.
There's a couple topics that touch on each other.
The first thing just to recap was that I wanted to show you when we do joints.
Any joints?
They're not fancy special tables.
They work just the same way as any other table.
So we can do things like group by an order by.
Then the next thing was that we saw inner joints.
If I go back, we saw this and then we also saw a left joint.
And the key difference, they both join data from two tables, but the key difference is what data they
join.
So we give them a condition.
But even given the same condition, in our case it was join on customer ID equals orders.
Customer ID this middle part and on inner join.
That's all we get is the exact overlap.
On a left join.
We also get the left part in addition.
So every customer in our case which can be useful if we wanted to see things like which customers hadn't
ordered things, which is what we got here.
So hopefully that explanation made some sense.
I totally understand.
If it didn't, this is a a tough thing to really wrap your head around at first.
Now, it's not terribly difficult once you get going with it, but understanding it, it can be super
intimidating.
So if you're in that situation, we will have exercises coming up.
And just one more video after this.
And I recommend you try and go through those.
And if that's not enough, then absolutely ask for help in the forums here.
This is one of the crucial things that, in my experience, sometimes take students a couple of maybe
hours, a couple of days, not not like 12, 20 hour days.
I just mean sleeping on it once or twice to help kind of get some space in your brain.
Anyways, hopefully you're able to kind of get where I'm going with this next step.
We're going to see our last type of join, which is a right join.
Very simple.
Well, it's not simple, but very easy to grasp if you understand left joints.