In this video, we're going to answer a simple question.
Back when we typed describe table names to describe cats.
What's up with this part?
What is this Noel column here in this little table?
And why is there two yes's for a name and age?
So this video is all about null the concept of null and working with NULL in SQL.
So to start off, let's talk about what NULL means.
No, basically means that the value is unknown.
It's SQL as way of saying I don't know what this is or there is no specified value.
An important note is that it does not mean zero.
Some people often think null means zero.
It doesn't in this context.
So to illustrate the significance of these having a yes here, it basically means that these are permitted
to be null.
It's okay if they have an unknown value and that's that happens by default.
To illustrate, though, that that is the case, here's a little bit of code right now.
We could run this command.
We have a catch table with name and age, but we could do insert into cats and only specify a name.
And in this case, I use Alabama.
Not sure who names her cat Alabama, but you could probably say who names are cat blue, which is what
I named my cat.
So we could run this command and age has no specified value.
So in fact, let's try it right now.
Make some space insert into cats.
Name.
Oops, values.
What is this siren?
Al Bama.
Just like that.
We don't get any warnings or anything.
Saying that name or that age is empty.
Nothing like that.
Now, if we do select star from Cat, you'll see that we have a new cat whose name is Alabama, but
age is null.
And again, that's because we have null as a permitted value, or it's technically not a value, but
age is allowed to be null.
So his name, so I could do the same thing, or an even more extreme version, I could run this insert
into cats nothing, values, nothing.
And if I did that, take a moment to guess.
Insert into cats nothing.
Values, nothing.
What do you think will happen?
Let's do a select star.
Now we have cat null or name null h null.
So then the question becomes how do we prevent that?
How do we require that name is not null and that age is not null?
And the answer?
Is not know.
So what we can do is specify when we actually define a table that something, let's say a field like
name is not null.
And we use this syntax right here.
So here's an example for a cat to table and I'll be using this convention.
Cat two Cats three So in a Cat two table or 2.0 version, when we define it, we would say name is char.
This time I used 100, but the limit doesn't matter really.
Followed by not null.
So that means that it will not be permitted to be empty.
And then age is also an age is an integer who is also not null.
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
this on separate lines.
Name is bar char 100.
So okay, this one, you can have longer names and then we'll have not null.
Followed by age is an int also not null.
We hit enter.
Now let's start off by using describe cat to.
This time under null for both fields says no.
So that's one way to tell that these are not allowed to be known.
And just to emphasize this, I made a slide.
Notice the difference here.
So now let's actually put it to the test.
Let's try to insert into cats, too.
And this time we'll only do name and we'll leave off age.
And then we'll have values and name for this one will be Texas in keeping with the state theme.
What do you think will happen?
Insert into cats where age is not allowed to be null.
But we're not specifying age.
So it says query.
We didn't get an error message and we got a warning.
So let's do.
Show warnings.
Warning field age doesn't have a default value.
So what it's telling me is that I left age off.
I didn't specify it.
So what it was going to do is make it null.
But then, oh no, it's not allowed to be null.
So I need to go find a default value and there's not a default value specified.
So if we look and you can probably guess our next our next video will be on default values.
But if we do select star from Cat two, you can see it automatically defined zero.
So in the next video we'll talk about why that happened.
But the way that it works, if it's not allowed to be no, you don't have a default, it's going to
be zero.
Now let's try the same thing with insert into cat two.
And this time only age is specified and age will be seven.
I get the same query.
One morning show warnings.
This time it says field name doesn't have a default value.
Let's do our select star.
Notice what we get right here.
Earlier.
See how far back I can go?
We had null when we didn't specify a name.
When we insert it into cats, just regular cats.
Not cats too.
But now when I don't specify a name, it doesn't say no.
And this is an empty string, so there's a difference.
NULL means that there is no value at all.
We didn't specify.
Empty string is just a type of a var char that just has nothing in it.
So you can think of it as this basically.
Okay.
So next we'll talk about how we specify these default values.