Several considerations need to be taken regarding SQL columns allowingNULL values.
SQL Basics about NULL
The SQL concept of NULL means "no value" or "undefined value". This is used inSQL to insert or update table rows and omit values for some fields that are not mandatory.
The NULL value is not zero: Zero is a real, numeric and non-null value.
Depending on the database engine, an empty string is null, or non-null. For more details, seeEmpty strings and NULLs.
The [NOT] NULL column constraint
SQL table columns can be defined with the constraint NULL or NOTNULL (to deny NULL values):
CREATE TABLE customer ( cust_id INTEGER NOT NULL PRIMARY KEY, cust_name VARCHAR(50) NOT NULL, cust_creation DATETIME YEAR TO SECOND NOT NULL, cust_address VARCHAR(100) NULL, ...)When omitting the NULL or NOT NULL constraint in CREATETABLE, most database engines define the column as nullable. However, when no constraint isspecified, some database brands such as Microsoft™ SQLServer deny NULL values in their default configuration. There is an SQL Serverdatabase option for that: See SQL table definition in the SQL Server adaptationguide.
Mandatory information should always be defined with a NOT NULL constraint. Forexample, in an SQL table storing customer information, columns such as the customer name, the rowcreation date/timestamp, columns referencing other tables rows with foreign keys, and obviously theprimary key column, should be defined with the NOT NULL constraint. It is also agood practice to deny NULL values for flag/option columns like a Yes/No status, orgender information.
On the other hand, secondary data such as a comment field, or fields that can only be assignedafter the first row creation (such as update timestamps), should accept NULLvalues.
Comparing NULL values
When NULL values enter in the game, it is important to understand that in means"no value".
When used in comparison, arithmetic expressions, the SQL engine can't do anything withNULL. In such case, the expression usually evaluates to NULL aswell.
Important:
Do not use the = or != operator tocheck for NULL values!
The standard SQL operator to check for NULL values is the IS [NOT]NULLoperator.
SELECT COUNT(*) FROM customers WHERE cust_comment IS NULLFurthermore, when manipulating non-null values, keep in mind what happens with an operator, whenNULL values can show up. In the next SQL example, without knowing the semantics ofthe != comparison operator, it's not obvious to guess if the lastSELECT statement is supposed to return only the row with value200:
CREATE TABLE tab1 ( col1 INTEGER );INSERT INTO tab1 VALUES (NULL);INSERT INTO tab1 VALUES (100);INSERT INTO tab1 VALUES (300);SELECT SUM(col1) FROM tab1;SELECT AVG(col1) FROM tab1;SELECT * FROM tab1 WHERE col1 != 100;To simplify the usage of SQL columns that can be NULL, the most database enginesprovide SQL functions to replace a potential NULL value by a default, non-null value:
| Database Server Type | NVL() | ISNULL() | IFNULL() | COALESCE() |
|---|---|---|---|---|
| IBM® Informix® | Yes | No | No | Yes |
| IBM Netezza | Yes | Yes | No | Yes |
| Microsoft SQL Server | No | Yes | No | Yes |
| Oracle® MySQL | No | No | Yes | Yes |
| MariadDB | Yes | No | Yes | Yes |
| Oracle DatabaseServer | Yes | No | Yes | Yes |
| PostgreSQL | No | No | No | Yes |
| SAP HANA® | No | No | Yes | Yes |
| SQLite | No | No | Yes | Yes |
| Dameng® | Yes | Yes | No | Yes |
As you can see, the COALESCE() function, which is standard ANSI, issupported by all database engine brands supported by Genero, and since it follows simple SQLfunction syntax, it can be used in static SQL.
Sorting NULL values
When sorting SQL rows with the ORDER BY clause on a column that acceptsNULL values, pay attention to the way the database engine considers nulls versusnon-null values when sorting.
For example, with Informix, when using ORDER BY colname[ASC], rows with NULL appear at the top of the result set. This isdifferent with PostgreSQL, where null values by default sort as if larger than other non-nullvalue.
| Database Server Type | NULL rows position (ASC) |
|---|---|
| IBM Informix | First |
| Microsoft SQL Server | First |
| Oracle MySQL | Last |
| MariadDB | First |
| Oracle DatabaseServer | Last |
| PostgreSQL | Last |
| SAP HANA | First |
| SQLite | First |
Consider using NOT NULL constraints for ORDER BY sort columns,when the position of the rows with NULL values matters.
