Null Value - SQL Considerations (Magic xpa 2.x)

Nulls represent missing and unknown data. All SQL databases support null values. A field that has a null value is different from a blank field in an ISAM file. Null means that the value is not known. Null values require special handling. If you attempt to do arithmetical operations on a numeric column and one or more of the values are null, then the result will be null. If an alpha field allows null values, and you select all records in which the alpha field is blank, records with the null value in the alpha field will NOT be selected.

Null values do not participate in index searches. It is highly recommended not to define indexes on columns that are null-allowed.

For example, this SELECT statement

SELECT *
FROM Table1
WHERE Fld1>=4 or Fld1<4

will return all the records in Table1 except for the records where Fld1 is null.

Nulls are represented in a different sort value in each database. For example, if we perform this SELECT statement in Oracle and in MSSQL, we’ll receive a different order of records in each database.

SELECT *
FROM Table1
Order by Fld1 ASC

In Oracle nulls are saved as the highest value in the database, so records with nulls in column Fld1 will appear as the last records of this SELECT statement.

In MSSQL nulls they are saved as the lowest value in the database, so records with nulls in column Fld1 will appear as the first records of this SELECT statement.