In SQL, NULL represents a missing, unknown, or inapplicable value. It is not a specific value like zero (0) or an empty string (''), but rather a placeholder indicating the absence of data. For example, if a customer’s middle name is not provided, the field can be set to NULL to reflect that the information is missing, not that the person has no middle name.
Key Points About NULL:
NULL is not equal to anything, including itself. Comparisons like
NULL = NULLreturn UNKNOWN, not TRUE.You cannot use standard operators (e.g.,
=,<>) to test for NULL values. Instead, useIS NULLorIS NOT NULLinWHEREclauses.Arithmetic and string operations involving NULL typically result in NULL. For example,
10 + NULLreturns NULL.SQL uses three-valued logic (3VL): results can be TRUE, FALSE, or UNKNOWN. This affects query outcomes, especially in
WHEREconditions.NULL is not a value, but a marker. It is a reserved keyword in SQL and is used to represent missing or undefined data in a database field.
💡 Example:
SELECT * FROM Customers WHERE MiddleName IS NULL;
This query retrieves all records where the middle name is missing or not provided.
Why NULL Matters:
It helps maintain data integrity by distinguishing between no data and known values like 0 or ''.
Misunderstanding NULL can lead to unexpected query results, especially in filtering, joins, and aggregations.
📌 Note: While some databases (like Oracle) treat empty strings and NULLs similarly, it's best practice to treat them as distinct and avoid relying on such behavior.
Factsheet
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.
Lets say i have a Excel sheet , col : name and ID . some rows cells in "name" are empty in datasheet.
now sql pulls this data, will the empty cells be filled with a null value by default?
or is null a unique type of character that is only used to overwrite existing values , so when system reads it it thinks its empty?