Factsheet
Need some knowledge on NULL and NOT NULL
What is NULL in SQL? - Stack Overflow
oop - Are nulls in a relational database okay? - Stack Overflow
feature comparison - When to use NULL and when to use an empty string? - Database Administrators Stack Exchange
Videos
Where and why exactly a null is used?
What is exactly null and not null? To my understanding Not null we use when its mandatory to insert some value in that field, also when we give check constraint so by default the column will be not null right?
By adding new column through alter method default values are null, so how would I be able to insert values in it and is it right to give not null constraint to that new column while adding through alter method, basically when null and when not null to be used?...
god this is so confusing please help me, ik im asking alot but im really confused
In simple worlds you can say that Null is not a data value, but a marker for an unknown value.
So any mathematical operations performed on NULL will result in NULL. For example,
10 + NULL = NULL
Similarly if you do string concatenation with string you get:-
'String ' || NULL || 'Concatenation' -- Result is NULL
So you can say that Null means either "not applicable" or "don't know": it is not the same as zero (0) or any other default value, but more importantly, null is treated quite differently from other values in SQL, because it literally has no value.
An example to explain what it means when we say that NULL means UNKNOWN VALUE:
StudentName TestResult
X 78
A 89
B 67
C NULL
So you can see that the student C got NULL marks in the test. So what does that mean?
Now one can say that the student does not sit in the test or it may be that the student's data is not avaialable. But it definitely does not mean that the student got 0(as if you assign 0 to the student then it would mean that the student appeared in the test and got zero) marks in the test. All we can say that the data for the student is
UNKNOWN or NULL
A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.
If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.
NULL values are treated differently from other values.
NULL is used as a placeholder for unknown or inapplicable values. Read more about this here.
Nulls are negatively viewed from the perspective of database normalization. The idea being that if a value can be nothing, then you really should split that out into another sparse table such that you don't require rows for items which have no value.
It's an effort to make sure all data is valid and valued.
In some cases having a null field is useful, though, especially when you want to avoid yet another join for performance reasons (although this shouldn't be an issue if the database engine is setup properly, except in extraordinary high performance scenarios.)
One argument against nulls is that they don't have a well-defined interpretation. If a field is null, that could be interpreted as any of the following:
- The value is "Nothing" or "Empty set"
- There is no value that makes sense for that field.
- The value is unknown.
- The value hasn't been entered yet.
- The value is an empty string (for databases that don't distinguish between nulls and empty strings).
- Some application-specific meaning (e.g., "If the value is null, then use a default value.")
- An error has occurred, causing the field to have a null value when it really shouldn't.
Some schema designers demand that all values and data types should have well-defined interpretations, therefore nulls are bad.
Let's say that the record comes from a form to gather name and address information. Line 2 of the address will typically be blank if the user doesn't live in apartment. An empty string in this case is perfectly valid. I tend to prefer to use NULL to mean that the value is unknown or not given.
I don't believe the physical storage difference is worth worrying about in practice. As database administrators, we have much bigger fish to fry!
I do not know about MySQL and PostgreSQL, but let me treat this a bit generally.
There is one DBMS namely Oracle which doesn't allow to choose it's users between NULL and ''. This clearly demonstrates that it is not necessary to distinguish between both. There are some annoying consequences:
You set a varchar2 to an empty string like this:
Update mytable set varchar_col = '';
the following leads to the same result
Update mytable set varchar_col = NULL;
But to select the columns where the value is empty or NULL, you have to use
select * from mytable where varchar_col is NULL;
Using
select * from mytable where varchar_col = '';
is syntactically correct, but it never returns a row.
On the other side, when concatenating strings in Oracle. NULL varchars are treated as empty strings.
select NULL || 'abc' from DUAL;
yields abc. Other DBMS would return NULL in these cases.
When you want to express explicitly, that a value is assigned, you have to use something like ' '.
And you have to worry whether trimming not empty results in NULL
select case when ltrim(' ') is null then 'null' else 'not null' end from dual
It does.
Now looking at DBMS where '' is not identical to NULL (e.g. SQL-Server)
Working with '' is generally easier and in most case there is no practical need to distinguish between both. One of the exceptions I know, is when your column represents some setting and you have not empty defaults for them. When you can distinguish between '' and NULL you are able to express that your setting is empty and avoid that the default applies.
It's convenient for the way SQL is typically used. Consider this statements:
SELECT people.name, cars.model FROM people
INNER JOIN cars
ON people.car_licenceplate = cars.licenceplate
If null = null, then this would return all pairs of people with no license plate with all unregistered cars in the database, a usually undesirable result.
It's particularly convenient that, even if you use any null value even in a more complex expression, you won't get a value back, even if other values may also happen to be null. In other languages you'd need to null check everything in advance to get that behavior, having it by default is very convenient for the type of things SQL is typically used for.
null in SQL is exempt from a lot of other rules too. For example they are excluded from unique constraints. All indicating it represents more the absence of a value rather than a special value.
Some other languages do also have a ThreeValueBoolean or a similar type that behaves more like a SQL null, though only for booleans. Also most every language has similar non self-equality for NaN. It's not a concept unique to SQL.
One way to look at this is to compare these two questions:
- Is value A definitely the same as value B?
- Is value A definitely different from value B?
On the face of it, these are symmetrical: if question 1 is true, question 2 is false, and vice versa.
But what if both A and B are missing or invalid data points?
- False. We can't know for sure that the two missing or invalid data points are the same.
- False. We can't know for sure that the two missing or invalid data points are different.
That puts us in a peculiar position: A = B and A <> B should both be false, but that means that NOT (A = B) is no longer the same as A <> B, which is surprising.
SQL handles this by returning a further NULL - if the data for A and B is missing, then the information about whether they are the same or different is also missing. This is consistent with other operations on NULL, e.g. NULL + NULL is NULL, because adding two unknown numbers gives you a third unknown number. And since that also includes boolean negation - if A is NULL, then NOT A is also NULL, the result of NOT (A = B) is always the same as A <> B, as we'd intuitively expect.
However, there are situations where we want to ask the strict negation of those questions:
- Is value A not definitely the same as value B? (Strict inverse of question 1)
- Is value A not definitely different from value B? (Strict inverse of question 2)
For these, SQL provides the DISTINCT FROM and NOT DISTINCT FROM operators.
More commonly, you want to know explicitly that a particular value is or is not null, for which there are the operators IS NULL and IS NOT NULL.