OK I hope you're still with us.
I really hope you sleep at 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 JOIN.
There's the left and right.
They worked very similarly So once you know 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 and another table B and then we select
all the records from A and B where the join condition is met.
But there's another type of choice.
Before we 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 in his orders.
And Patty Davis in her orders I can do just any regular ask you well 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 By.
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
amount if you want it to.
And now we have from the lowest order up to the highest order or we could order a pie order date.
So that's one thing we can do.
But we can also do things like group buy.
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.
OK.
Groups and by who ordered them and then take that and find the average amount they spent or the total
amount.
But we start there let's identify the biggest spender in our entire database which is pretty easy to
do if you just look here and add things up.
But let's say we had you know ten thousand records.
How do we find the biggest spender.
So Will group buy.
And how do we group them.
Well we want to group by our customer right place the order first name could work.
But that assumes that we only have one beti 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 Khama 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 dot customer id so gripped by that and if we hit enter.
I remember when we do group high 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 you 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 ideas here that we
weren't showing and just grouping these two together these two together and this one on its own.
We're not actually displaying that.
OK.
So we can group by that like we did and then we want to do is totally together the amount.
So rather than just amount to do some amount and I'm going to clean this up a little bit and just 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 if we could order by total spent.
Move our semi-colon.
Now you can see and actually let's do descending.
Now you can see OK George Michael is definitely our biggest spender.
Thirteen point seventeen 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.
OK.
So I just want 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 a little better.
The colors are still awful I'm sorry.
So what does that 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 enter.
But then it also does everything from the left table from customers.
In our case.
So let's test it out over in cloud 9.
Have a comment here for my join.
And let's go take one of our joint that we did earlier like let's do this first one.
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 most common type.
What it does is we know it takes customers where the I.D. matches customer I.D. and that's all we get.
And 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.
And Boy George another order he placed George Michael two orders that match.
But then we have two users who never placed orders.
David Bowie in Blue Steel do not have orders.
And so we end up with David Bowie but still 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 impeccable match.
So for David Bowie and blue still there isn't one.
And it fills in NO but for Boy George and George Michael and Bette 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 with it beginning.
Boy George is there a matching order.
Yes.
This one here right here and this one.
So put those together.
George Michael Id have to.
Is there any marching 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 blues 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 access to 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 OK 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 you know maybe send an e-mail to the people who have spent a lot.
OK thanks for being a loyal customer versus people who haven't spent.
I could go through and send an e-mail.
Here's a coupon for your first purchase 10 percent off 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 OK but we have Knoll's here.
So is there a way to change that.
Let's say I want to do the same thing.
We had a period where we had our high spenders.
Right.
So we selected and we summed all the orders based off of grouping them by customer IDs 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 NOLs.
So if we do this it's like first name last name order date and amount from customers left join orders
and we do a group by customers at this time.
So that will group by customers that we have these two grouped these two groups on his own on his own
on her.
And actually it is a girl I'm sorry.
OK.
So if we do this we won't see much difference.
Well we will see a difference but it's not very meaningful.
What am I missing.
Extra semi-colon not that useful because it just condenses our data and hides stuff.
But if we go through and format this again
put 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.
OK so this is accurate.
That's accurate.
We still have no hair which it can work but it would be nice if instead of just said 0 because they've
spent $0.
So how do we do that.
And this is not an energy is not a join specific thing in any way.
This is just an interesting problem.
How do we get instead of no how do I replace that with zero.
And there's a couple of ways.
The first really length the awful way is to use a case statement so I could have a case statement where
I checked is some amount is no number I can't just say equals nil but if I said it's is null then do
0 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 no and haven't shown this to you yet because
we didn't really have many cases where we had no data.
But here's one.
So what if does it takes two arguments.
The first one is the thing you want it to check if it is known or not.
So we want to check is some of amount.
No.
So here it's not right it's not.
No it's not.
No.
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 no.
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 spend.
Again OK.
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.
Now you can see we have zero.
OK.
So we kind of talked about a lot.
There's a couple of 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 grouped by an order by then the next thing was that we saw inner joints.
I go back we saw this and we also saw a left join.
And the key difference.
They both join data from two tables with the key differences what data they join.
So we give him a condition.
But even given the same condition in our case it was a join on customer ID equals orders customer ID.
This middle part in an 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 in 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 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 up we're going to see our last type of join which is right join.
Very simple can so simple and very easy to grasp if you understand left joints.