1
00:00:00,240 --> 00:00:05,760
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

2
00:00:05,760 --> 00:00:10,770
you a new function, a built in function we have not seen called if null.

3
00:00:11,010 --> 00:00:14,700
So let's do a join with a group by specifically a left join.

4
00:00:14,820 --> 00:00:21,990
So I'm going to have customers on the left and then I will join that with orders and that will show

5
00:00:21,990 --> 00:00:27,990
me, as we've already seen, which customers have not placed any orders, along with all the customers

6
00:00:27,990 --> 00:00:31,500
who have placed orders, it will match them up with their corresponding orders.

7
00:00:31,620 --> 00:00:34,710
So let's do a select first name.

8
00:00:35,490 --> 00:00:36,450
Last name.

9
00:00:37,590 --> 00:00:38,640
We even need order date.

10
00:00:38,640 --> 00:00:44,250
Now let's just do amount from and then of course, we can't get that all from customers.

11
00:00:44,250 --> 00:00:50,070
We'll do it from customers and then left join orders on.

12
00:00:50,520 --> 00:00:53,610
And just to mix it up, let's switch the order of this.

13
00:00:53,610 --> 00:00:54,560
It doesn't matter though.

14
00:00:54,570 --> 00:00:57,270
Customers ID is equal to orders.

15
00:00:57,270 --> 00:00:59,190
DOT, Customer ID.

16
00:00:59,430 --> 00:00:59,760
All right.

17
00:00:59,760 --> 00:01:01,980
So this is what we did in the previous video, essentially.

18
00:01:01,980 --> 00:01:05,099
I mean, we have some different columns, but same principle.

19
00:01:05,099 --> 00:01:12,300
It's a left join every row from the customer's table matched up with any overlap from the orders table.

20
00:01:12,750 --> 00:01:16,530
And then if there is no overlap, we get null in this case for amount.

21
00:01:16,740 --> 00:01:23,940
So what I want to do now is group by first name and last name, and this will allow me to find out information

22
00:01:24,150 --> 00:01:28,410
in aggregate about our customers and the orders they've placed or have not placed.

23
00:01:28,410 --> 00:01:35,490
So I'll do a group by first name, comma, last name, and if I just do that, it's not going to help

24
00:01:35,490 --> 00:01:36,180
us much.

25
00:01:36,570 --> 00:01:43,260
But instead of doing amount, why don't we do some of amount or average?

26
00:01:43,620 --> 00:01:44,880
But let's start with some.

27
00:01:44,880 --> 00:01:48,960
And this tells us how many dollars each individual customers spent.

28
00:01:48,960 --> 00:01:51,450
We already saw how to do this with an inner join.

29
00:01:51,450 --> 00:01:54,720
What's different now is that I have no values present.

30
00:01:54,990 --> 00:01:59,750
So instead of null, maybe I want to sub in the value zero, right?

31
00:01:59,760 --> 00:02:03,690
That would be the amount they've spent if that's what this is supposed to be.

32
00:02:04,060 --> 00:02:05,790
Right from customers.

33
00:02:05,790 --> 00:02:11,760
Some who call as money spent or something like that.

34
00:02:11,760 --> 00:02:19,080
This now just rerun it again money spent instead of having null there of nothingness.

35
00:02:19,080 --> 00:02:21,090
Let's translate that to zero.

36
00:02:21,090 --> 00:02:29,460
And one way of doing this is by using a function I have not shown you yet called if no in the way that

37
00:02:29,460 --> 00:02:36,660
if no works is that we provide some thing that it will evaluate some expression and for every row where

38
00:02:36,660 --> 00:02:37,410
it gets null.

39
00:02:37,410 --> 00:02:38,910
So that's the first piece.

40
00:02:38,910 --> 00:02:46,080
So I'll say some amount for every row that you get null, it will sub in the second value after the

41
00:02:46,080 --> 00:02:46,560
comma.

42
00:02:46,560 --> 00:02:52,800
So this is saying, all right, do this sum and if you get null, replace it with zero.

43
00:02:52,800 --> 00:02:54,390
So that's what I'm going to do right here.

44
00:02:54,390 --> 00:02:58,080
If null, try to some amount.

45
00:02:58,470 --> 00:03:01,950
And if you get null, replace it with zero.

46
00:03:02,910 --> 00:03:08,370
And I run this now we see zero for David Bowie and Blue Steel.

47
00:03:08,370 --> 00:03:14,280
They have not spent any money and this now makes it easier to do things like do math with this sort.

48
00:03:14,490 --> 00:03:16,650
Anyway, just one to show another example.

49
00:03:16,680 --> 00:03:21,120
Simple enough, but we can combine some of the features we've seen all together.

50
00:03:21,120 --> 00:03:26,760
The newest piece here is this If null function, it just takes some expression, evaluates it, and

51
00:03:26,760 --> 00:03:30,450
if it's no, it replaces it with whatever we provide here.

52
00:03:30,960 --> 00:03:31,340
Okay.

53
00:03:31,560 --> 00:03:35,040
Next we're going to move on to write joints and then an exercise.