1
00:00:00,120 --> 00:00:03,120
In this video, we're going to answer a simple question.

2
00:00:03,300 --> 00:00:08,010
Back when we typed describe table names to describe cats.

3
00:00:09,400 --> 00:00:10,780
What's up with this part?

4
00:00:10,810 --> 00:00:14,350
What is this Noel column here in this little table?

5
00:00:14,860 --> 00:00:18,340
And why is there two yes's for a name and age?

6
00:00:18,880 --> 00:00:24,490
So this video is all about null the concept of null and working with NULL in SQL.

7
00:00:24,670 --> 00:00:27,340
So to start off, let's talk about what NULL means.

8
00:00:28,030 --> 00:00:31,500
No, basically means that the value is unknown.

9
00:00:31,510 --> 00:00:35,830
It's SQL as way of saying I don't know what this is or there is no specified value.

10
00:00:36,490 --> 00:00:39,570
An important note is that it does not mean zero.

11
00:00:39,580 --> 00:00:41,770
Some people often think null means zero.

12
00:00:41,770 --> 00:00:43,420
It doesn't in this context.

13
00:00:43,420 --> 00:00:48,940
So to illustrate the significance of these having a yes here, it basically means that these are permitted

14
00:00:48,940 --> 00:00:49,960
to be null.

15
00:00:49,990 --> 00:00:54,790
It's okay if they have an unknown value and that's that happens by default.

16
00:00:54,820 --> 00:01:00,220
To illustrate, though, that that is the case, here's a little bit of code right now.

17
00:01:00,220 --> 00:01:01,930
We could run this command.

18
00:01:02,110 --> 00:01:09,310
We have a catch table with name and age, but we could do insert into cats and only specify a name.

19
00:01:09,310 --> 00:01:11,800
And in this case, I use Alabama.

20
00:01:12,090 --> 00:01:16,930
Not sure who names her cat Alabama, but you could probably say who names are cat blue, which is what

21
00:01:16,930 --> 00:01:17,830
I named my cat.

22
00:01:18,160 --> 00:01:23,080
So we could run this command and age has no specified value.

23
00:01:23,080 --> 00:01:25,120
So in fact, let's try it right now.

24
00:01:25,840 --> 00:01:29,410
Make some space insert into cats.

25
00:01:31,570 --> 00:01:32,380
Name.

26
00:01:32,800 --> 00:01:35,590
Oops, values.

27
00:01:37,060 --> 00:01:38,620
What is this siren?

28
00:01:40,180 --> 00:01:41,620
Al Bama.

29
00:01:42,430 --> 00:01:43,420
Just like that.

30
00:01:45,510 --> 00:01:47,010
We don't get any warnings or anything.

31
00:01:47,010 --> 00:01:49,440
Saying that name or that age is empty.

32
00:01:49,620 --> 00:01:50,670
Nothing like that.

33
00:01:50,910 --> 00:01:59,670
Now, if we do select star from Cat, you'll see that we have a new cat whose name is Alabama, but

34
00:01:59,670 --> 00:02:00,820
age is null.

35
00:02:00,840 --> 00:02:08,789
And again, that's because we have null as a permitted value, or it's technically not a value, but

36
00:02:08,789 --> 00:02:10,229
age is allowed to be null.

37
00:02:10,229 --> 00:02:17,280
So his name, so I could do the same thing, or an even more extreme version, I could run this insert

38
00:02:17,280 --> 00:02:21,300
into cats nothing, values, nothing.

39
00:02:21,480 --> 00:02:25,440
And if I did that, take a moment to guess.

40
00:02:26,190 --> 00:02:30,300
Insert into cats nothing.

41
00:02:30,300 --> 00:02:31,740
Values, nothing.

42
00:02:32,040 --> 00:02:33,240
What do you think will happen?

43
00:02:34,290 --> 00:02:35,790
Let's do a select star.

44
00:02:36,360 --> 00:02:40,080
Now we have cat null or name null h null.

45
00:02:40,320 --> 00:02:43,040
So then the question becomes how do we prevent that?

46
00:02:43,050 --> 00:02:47,100
How do we require that name is not null and that age is not null?

47
00:02:47,910 --> 00:02:48,930
And the answer?

48
00:02:49,870 --> 00:02:51,640
Is not know.

49
00:02:51,970 --> 00:02:58,660
So what we can do is specify when we actually define a table that something, let's say a field like

50
00:02:58,660 --> 00:03:00,700
name is not null.

51
00:03:01,090 --> 00:03:03,100
And we use this syntax right here.

52
00:03:03,100 --> 00:03:09,340
So here's an example for a cat to table and I'll be using this convention.

53
00:03:09,550 --> 00:03:19,080
Cat two Cats three So in a Cat two table or 2.0 version, when we define it, we would say name is char.

54
00:03:19,090 --> 00:03:22,330
This time I used 100, but the limit doesn't matter really.

55
00:03:22,720 --> 00:03:24,490
Followed by not null.

56
00:03:25,210 --> 00:03:28,930
So that means that it will not be permitted to be empty.

57
00:03:29,470 --> 00:03:34,600
And then age is also an age is an integer who is also not null.

58
00:03:35,470 --> 00:03:47,020
So if we try that right now, I'm going to create a table cat two and we're going to have and I'll do

59
00:03:47,020 --> 00:03:48,300
this on separate lines.

60
00:03:49,180 --> 00:03:53,590
Name is bar char 100.

61
00:03:54,310 --> 00:03:58,390
So okay, this one, you can have longer names and then we'll have not null.

62
00:03:59,910 --> 00:04:06,540
Followed by age is an int also not null.

63
00:04:09,070 --> 00:04:09,910
We hit enter.

64
00:04:10,150 --> 00:04:15,820
Now let's start off by using describe cat to.

65
00:04:16,970 --> 00:04:21,980
This time under null for both fields says no.

66
00:04:22,250 --> 00:04:26,060
So that's one way to tell that these are not allowed to be known.

67
00:04:26,450 --> 00:04:29,450
And just to emphasize this, I made a slide.

68
00:04:29,480 --> 00:04:30,570
Notice the difference here.

69
00:04:30,590 --> 00:04:33,800
So now let's actually put it to the test.

70
00:04:33,980 --> 00:04:38,450
Let's try to insert into cats, too.

71
00:04:38,990 --> 00:04:42,440
And this time we'll only do name and we'll leave off age.

72
00:04:43,280 --> 00:04:51,470
And then we'll have values and name for this one will be Texas in keeping with the state theme.

73
00:04:53,930 --> 00:04:55,250
What do you think will happen?

74
00:04:55,880 --> 00:05:00,140
Insert into cats where age is not allowed to be null.

75
00:05:00,440 --> 00:05:02,420
But we're not specifying age.

76
00:05:03,850 --> 00:05:05,010
So it says query.

77
00:05:05,860 --> 00:05:08,920
We didn't get an error message and we got a warning.

78
00:05:09,160 --> 00:05:09,860
So let's do.

79
00:05:09,880 --> 00:05:10,810
Show warnings.

80
00:05:11,590 --> 00:05:15,310
Warning field age doesn't have a default value.

81
00:05:15,430 --> 00:05:19,360
So what it's telling me is that I left age off.

82
00:05:19,360 --> 00:05:20,530
I didn't specify it.

83
00:05:20,530 --> 00:05:22,630
So what it was going to do is make it null.

84
00:05:22,630 --> 00:05:25,090
But then, oh no, it's not allowed to be null.

85
00:05:25,090 --> 00:05:30,400
So I need to go find a default value and there's not a default value specified.

86
00:05:30,940 --> 00:05:37,210
So if we look and you can probably guess our next our next video will be on default values.

87
00:05:37,210 --> 00:05:44,890
But if we do select star from Cat two, you can see it automatically defined zero.

88
00:05:45,130 --> 00:05:47,590
So in the next video we'll talk about why that happened.

89
00:05:47,770 --> 00:05:52,300
But the way that it works, if it's not allowed to be no, you don't have a default, it's going to

90
00:05:52,300 --> 00:05:53,140
be zero.

91
00:05:53,530 --> 00:05:59,080
Now let's try the same thing with insert into cat two.

92
00:05:59,080 --> 00:06:03,850
And this time only age is specified and age will be seven.

93
00:06:06,270 --> 00:06:07,530
I get the same query.

94
00:06:07,980 --> 00:06:10,770
One morning show warnings.

95
00:06:11,190 --> 00:06:14,700
This time it says field name doesn't have a default value.

96
00:06:15,960 --> 00:06:17,430
Let's do our select star.

97
00:06:17,640 --> 00:06:19,020
Notice what we get right here.

98
00:06:19,830 --> 00:06:20,700
Earlier.

99
00:06:21,000 --> 00:06:22,500
See how far back I can go?

100
00:06:22,590 --> 00:06:26,400
We had null when we didn't specify a name.

101
00:06:26,400 --> 00:06:29,040
When we insert it into cats, just regular cats.

102
00:06:29,070 --> 00:06:30,030
Not cats too.

103
00:06:30,240 --> 00:06:33,750
But now when I don't specify a name, it doesn't say no.

104
00:06:33,900 --> 00:06:37,360
And this is an empty string, so there's a difference.

105
00:06:37,380 --> 00:06:39,660
NULL means that there is no value at all.

106
00:06:39,660 --> 00:06:40,860
We didn't specify.

107
00:06:41,700 --> 00:06:47,490
Empty string is just a type of a var char that just has nothing in it.

108
00:06:47,490 --> 00:06:50,040
So you can think of it as this basically.

109
00:06:50,390 --> 00:06:50,730
Okay.

110
00:06:50,760 --> 00:06:54,450
So next we'll talk about how we specify these default values.

