Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Null is a special state for the result no value. This is necessary because it's often important to distinguish between zero value or an empty string for a nonvalue result. For this lesson we're going to use the scratch database and I'm going to create a simple table here, and I'm going to insert a row. And we'll go and display that row with select. I noticed in the results in the a column the world null. Null is this special state in SQL. It's not really a value.
It's the lack of a value. So you cannot test for it as you would a normal value with the equal sign because it has no value. So if I say, SELECT ASTERISK FROM test WHERE a equals NULL, we'll get no result because there is no row where a is equal to NULL, because NULL is not a value so it cannot satisfy the equal to expression. NULL is a lack of value, so to test for NULL you say a is null instead of equal to null.
So if I test for where a is null, I get that row "Is Null" is a special condition in SQL just like "IS NOT NULL" is a special condition. Of course we get no result there because there aren't any rows in this table. Where it a is not null. So, just to make sure that we've got the distinction clear, I'm going to delete this row and I'm going to insert a new row. And, we'll give it values of zero.
Null and an empty string. Now, if I say select from test where b is Null, we get that row because b is Null in that row. But if I say select from test where b is equal to an empty string, I get no rows. Or if I test for where c equals null or c is null, I get no rows. If I check for where c is not null, then I get a row.
So, it's important to understand that null is not a value. Null is a state that represents a lack of value. Understanding this and how it works is an important part of writing good, robust SQL. Now, I'm going to drop this table So that the database is back in its original state for the next chapter.
Get unlimited access to all courses for just $25/month.Become a member