May 16, 2021 MariaDB
When using NULL values, keep in mind that they are unknown values. T hey are not empty strings or zeros, they are valid values. I n table creation, column specifications allow them to be set to accept empty values or reject them. S imply use the NULL or NOT NULL clause. T his has an app in the absence of record information, such as an ID number.
The value of a user-defined variable is NULL until it is explicitly assigned. S tored routine parameters and local variables allow the value to be set to NULL. W hen the authority variable does not have a default value, its value is NULL.
NULL is case-sensitive and has the following alias -
The standard comparison operator cannot be compared to NULL (e.g., s.g., u sed together because all comparisons to the NULL value return NULL instead of true or false. C omparisons with NULL or possibly containing it must use the NULL-SAFE operator.
Other available carriers have -
IS NULL - It tests NULL values.
IS NOT NULL - It confirms that there is no NULL value.
ISNULL - Returns a value of 1 when the NULL value is found and 0 when it does not exist.
COALESCE - Returns the first non-NULL value of the list, or the NULL value without a value.
In a sort operation, the NULL value has the lowest value, so the DESC order produces the NULL value at the bottom. M ariaDB allows a higher value to be set for the NULL value.
There are two ways to do this, as follows -
SELECT column1 FROM product_tbl ORDER BY ISNULL(column1), column1;
Another way -
SELECT column1 FROM product_tbl ORDER BY IF(column1 IS NULL, 0, 1), column1 DESC;
When any argument is NULL, the function usually outputs NULL. H owever, there are also functions dedicated to managing NULL values. T hey are -
IFNULL() - If the first expression is not NULL, it returns it. W hen it is evaluated as NULL, it returns a second expression.
NULLIF() - When the comparison expression is equal, it returns NULL, otherwise the first expression is returned.
Functions like SUM and AVG ignore NULL values.
An error occurs when a NULL value is inserted into a column declared NOT NULL. I n default SQL mode, the NOT NULL column inserts a default value based on the data type.
MariaDB manages NULL AUTO_INCREMENT different ways when the field is TIMESTAMP, a virtual column, or a virtual column. I nsertion in AUTO_INCREMENT column causes the next number in the sequence to be inserted into its position. I n the TIMESTAMP field, MariaDB assigns the current timestamp. I n the virtual column, the topics discussed later in this tutorial assign default values.
The UNIQUE index can contain many NULL values, but the primary key cannot be NULL.
When you modify a column using the ALTER command, MariaDB automatically assigns values if there is no NULL specification.