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.