I want to do one more example with a left join and we're going to add in group by and I'm going to show
you a new function, a built in function we have not seen called if null.
So let's do a join with a group by specifically a left join.
So I'm going to have customers on the left and then I will join that with orders and that will show
me, as we've already seen, which customers have not placed any orders, along with all the customers
who have placed orders, it will match them up with their corresponding orders.
So let's do a select first name.
Last name.
We even need order date.
Now let's just do amount from and then of course, we can't get that all from customers.
We'll do it from customers and then left join orders on.
And just to mix it up, let's switch the order of this.
It doesn't matter though.
Customers ID is equal to orders.
DOT, Customer ID.
All right.
So this is what we did in the previous video, essentially.
I mean, we have some different columns, but same principle.
It's a left join every row from the customer's table matched up with any overlap from the orders table.
And then if there is no overlap, we get null in this case for amount.
So what I want to do now is group by first name and last name, and this will allow me to find out information
in aggregate about our customers and the orders they've placed or have not placed.
So I'll do a group by first name, comma, last name, and if I just do that, it's not going to help
us much.
But instead of doing amount, why don't we do some of amount or average?
But let's start with some.
And this tells us how many dollars each individual customers spent.
We already saw how to do this with an inner join.
What's different now is that I have no values present.
So instead of null, maybe I want to sub in the value zero, right?
That would be the amount they've spent if that's what this is supposed to be.
Right from customers.
Some who call as money spent or something like that.
This now just rerun it again money spent instead of having null there of nothingness.
Let's translate that to zero.
And one way of doing this is by using a function I have not shown you yet called if no in the way that
if no works is that we provide some thing that it will evaluate some expression and for every row where
it gets null.
So that's the first piece.
So I'll say some amount for every row that you get null, it will sub in the second value after the
comma.
So this is saying, all right, do this sum and if you get null, replace it with zero.
So that's what I'm going to do right here.
If null, try to some amount.
And if you get null, replace it with zero.
And I run this now we see zero for David Bowie and Blue Steel.
They have not spent any money and this now makes it easier to do things like do math with this sort.
Anyway, just one to show another example.
Simple enough, but we can combine some of the features we've seen all together.
The newest piece here is this If null function, it just takes some expression, evaluates it, and
if it's no, it replaces it with whatever we provide here.
Okay.
Next we're going to move on to write joints and then an exercise.