Description\n-----------\n\nNULL represents an unknown value. It is _not_ an empty string (by default), or a zero value. These are all valid values, and are not NULL values.\n\nWhen a table is created or the format altered, columns can be specified as accepting NULL values, or not accepting them, with the NULL and NOT NULL clauses respectively.\n\nFor example, a customer table could contain dates of birth. For some customers, this information is unknown, so the value could be NULL.\n\nThe same system could allocate a customer ID for each customer record, and in this case a NULL value would not be permitted.\n\n``sql\nCREATE TABLE customer (\n id INT NOT NULL, \n date_of_birth DATE NULL\n...\n)\n`\n\nUser-defined variables are NULL until a value is explicitly assigned.\n\nStored routines parameters and local variables can always be set to NULL. If no DEFAULT value is specified for a local variable, its initial value will be NULL. If no value is assigned to an OUT parameter in a stored procedure, NULL is assigned at the end of the procedure.\n\nSyntax\n\nThe case of NULL is not relevant. (uppercase) is an alias for NULL.\n\nThe IS operator accepts UNKNOWN as an alias for NULL, which is meant for boolean contexts.\n\nComparison Operators\n\nNULL values cannot be used with most comparison operators. For example, =, >, >=, <=, <, or != cannot be used, as any comparison with a NULL always returns a NULL value, never true (1) or false (0).\n\n`sql\nSELECT NULL = NULL;\n+-------------+\n| NULL = NULL |\n+-------------+\n|        NULL |\n+-------------+\n\nSELECT 99 = NULL;\n+-----------+\n| 99 = NULL |\n+-----------+\n|      NULL |\n+-----------+\n`\n\nTo overcome this, certain operators are specifically designed for use with NULL values. To cater for testing equality between two values that may contain NULL values, there's <=>, NULL-safe equal.\n\n`sql\nSELECT 99 <=> NULL, NULL <=> NULL;\n+-------------+---------------+\n| 99 <=> NULL | NULL <=> NULL |\n+-------------+---------------+\n|           0 |             1 |\n+-------------+---------------+\n`\n\nOther operators for working with NULL values include IS NULL and IS NOT NULL, ISNULL (for testing an expression) and COALESCE (for returning the first non-NULL parameter).\n\nOrdering\n\nWhen you order by a field that may contain NULL values, any NULL values are considered to have the lowest value. So ordering in DESC order will see the NULL values appearing last. To force NULL values to be regarded as highest values, one can add another column which has a higher value when the main field is NULL. Example:\n\n`sql\nSELECT col1 FROM tab ORDER BY ISNULL(col1), col1;\n`\n\nDescending order, with NULL values first:\n\n`sql\nSELECT col1 FROM tab ORDER BY IF(col1 IS NULL, 0, 1), col1 DESC;\n`\n\nAll NULL values are also regarded as equivalent for the purposes of the DISTINCT and GROUP BY clauses.\n\nFunctions\n\nIn most cases, functions will return NULL if any of the parameters are NULL. There are also functions specifically for handling NULL values. These include IFNULL(), NULLIF() and COALESCE().\n\n`sql\nSELECT IFNULL(1,0); \n+-------------+\n| IFNULL(1,0) |\n+-------------+\n|           1 |\n+-------------+\n\nSELECT IFNULL(NULL,10);\n+-----------------+\n| IFNULL(NULL,10) |\n+-----------------+\n|              10 |\n+-----------------+\n\nSELECT COALESCE(NULL,NULL,1);\n+-----------------------+\n| COALESCE(NULL,NULL,1) |\n+-----------------------+\n|                     1 |\n+-----------------------+\n`\n\nAggregate functions, such as SUM and AVG ignore NULL values.\n\n`sql\nCREATE TABLE t(x INT);\n\nINSERT INTO t VALUES (1),(9),(NULL);\n\nSELECT SUM(x) FROM t;\n+--------+\n| SUM(x) |\n+--------+\n|     10 |\n+--------+\n\nSELECT AVG(x) FROM t;\n+--------+\n| AVG(x) |\n+--------+\n| 5.0000 |\n+--------+\n`\n\nThe one exception is COUNT(\\), which counts rows, and doesn't look at whether a value is NULL or not. Compare for example, COUNT(x), which ignores the NULL, and COUNT(), which counts it:\n\n`sql\nSELECT COUNT(x) FROM t;\n+----------+\n| COUNT(x) |\n+----------+\n|        2 |\n+----------+\n\nSELECT COUNT() FROM t;\n+----------+\n| COUNT() |\n+----------+\n|        3 |\n+----------+\n`\n\nAUTO_INCREMENT, TIMESTAMP and Virtual Columns\n\nMariaDB handles NULL values in a special way if the field is an AUTO_INCREMENT, a TIMESTAMP or a virtual column. Inserting a NULL value into a numeric AUTO_INCREMENT column will result in the next number in the auto increment sequence being inserted instead. This technique is frequently used with AUTO_INCREMENT fields, which are left to take care of themselves.\n\n`sql\nCREATE TABLE t2(id INT PRIMARY KEY AUTO_INCREMENT, letter CHAR(1));\n\nINSERT INTO t2(letter) VALUES ('a'),('b');\n\nSELECT  FROM t2;\n+----+--------+\n| id | letter |\n+----+--------+\n|  1 | a      |\n|  2 | b      |\n+----+--------+\n`\n\nSimilarly, if a NULL value is assigned to a TIMESTAMP field, the current date and time is assigned instead.\n\n`sql\nCREATE TABLE t3 (x INT, ts TIMESTAMP);\n\nINSERT INTO t3(x) VALUES (1),(2);\n`\n\nAfter a pause:\n\n`sql\nINSERT INTO t3(x) VALUES (3);\n\nSELECT FROM t3;\n+------+---------------------+\n| x    | ts                  |\n+------+---------------------+\n|    1 | 2013-09-05 10:14:18 |\n|    2 | 2013-09-05 10:14:18 |\n|    3 | 2013-09-05 10:14:29 |\n+------+---------------------+\n`\n\nIf a NULL is assigned to a VIRTUAL or PERSISTENT column, the default value is assigned instead.\n\n`sql\nCREATE TABLE virt (c INT, v INT AS (c+10) PERSISTENT) ENGINE=InnoDB;\n\nINSERT INTO virt VALUES (1, NULL);\n\nSELECT c, v FROM virt;\n+------+------+\n| c    | v    |\n+------+------+\n|    1 |   11 |\n+------+------+\n`\n\nIn all these special cases, NULL is equivalent to the DEFAULT keyword.\n\nInserting\n\nIf a NULL value is single-row inserted into a column declared as NOT NULL, an error will be returned. However, if the SQL mode is not strict (strict is the default), if a NULL value is multi-row inserted into a column declared as NOT NULL, the implicit default for the column type will be inserted (and NOT` the default value in the table definition). The implicit defaults are an empty string for string types, and the zero value for numeric, date and time types.\n\nBy default, both cases will result in an error.\n\nExamples\n--------\n\nCREATE TABLE nulltest (\n  a INT(11), \n  x VARCHAR(10) NOT NULL DEFAULT 'a', \n  y INT(11) NOT NULL DEFAULT 23\n);\n\nURL: https://mariadb.com/docs/server/reference/data-types/null-values