Null (SQL)
See also Null reference, Null pointer, Null pointer exception, Nullable type, Null-Safety
TLDR: In SQL, `NULL` represents the absence of a value or unknown data, introduced in the 1980s with the SQL-92 standard. Unlike an empty string or zero, `NULL` signifies that a value has not been assigned or is missing. It is a critical concept for database operations, requiring explicit handling in queries and logical conditions.
https://en.wikipedia.org/wiki/Null_(SQL)
Handling `NULL` in SQL requires special syntax since it does not behave like regular data. For example, comparisons involving `NULL` (e.g., `= NULL`) return unknown, not true or false. To check for `NULL` values, SQL provides the `IS NULL` and `IS NOT NULL` operators. Mismanagement of `NULL` values in queries can lead to logical errors or incorrect results, such as rows being excluded unintentionally from aggregations.
https://docs.microsoft.com/en-us/sql/t-sql/queries/null-and-isnull-transact-sql
NULL also impacts aggregate functions like `SUM` or `AVG`, as they ignore `NULL` values. To handle such scenarios, SQL provides functions like `COALESCE` and `IFNULL` to replace `NULL` with default values. For instance, `COALESCE(column_name, 0)` ensures that null values in a column are treated as zeros in computations, maintaining consistent results.
https://www.postgresql.org/docs/current/functions-conditional.html
Proper use of `NULL` in SQL involves understanding its implications in joins, comparisons, and constraints. For example, a `NOT NULL` constraint ensures that a column cannot have null values, enforcing data integrity. Careful handling of `NULL` is essential for avoiding issues such as data leakage or misinterpretation of results in critical applications.
https://dev.mysql.com/doc/refman/8.0/en/constraint-primary-key.html#constraint-primary-key-null
- Snippet from Wikipedia: Null (SQL)
In SQL, null or NULL is a special marker used to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL null serves to fulfill the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent null in database theory. In SQL,
NULLis a reserved word used to identify this marker.A null should not be confused with a value of 0. A null indicates a lack of a value, which is not the same as a zero value. For example, consider the question "How many books does Adam own?" The answer may be "zero" (we know that he owns none) or "null" (we do not know how many he owns). In a database table, the column reporting this answer would start with no value (marked by null), and it would not be updated with the value zero until it is ascertained that Adam owns no books.
In SQL, null is a marker, not a value. This usage is quite different from most programming languages, where a null value of a reference means it is not pointing to any object.