Understanding NULL In SQL: Unknown Values Vs Non-Existent Ones
NULL in SQL indicates an unknown value, not non-existence. Arithmetic operations with NULL yield unknown results. Databases may implement functions differently, e.g., PostgreSQL's greatest() & least() ignore null operands.
In the previous blog, I explained that a NULL in a column indicates an unknown value rather than a non-existent one. In a table row, values must exist, with a value or a null, when the value is unknown. In a normalized data model, missing values are represented by the absence of rows, not by null. However, outer joins can create rows for non-existing values. All their columns, including the primary key, contain NULLs. Arithmetic with NULL is simple when you consider it as unknown. Many operations have an unknown result when one operand is unknown: yugabyte=# with xyz(x,y,z) as ( values (...