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 NULL
values.
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]NULL
operator.
SELECT COUNT(*) FROM customers WHERE cust_comment IS NULL
Furthermore, 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.