Nulls In Sql

Nulls in SQL can sometimes be problematic. The author of most of this is from an Oracle SQL background, and almost completely missed that point that Oracle breaks the standard in this area. Thus this page should be called NullsInOracle.

Empty strings are treated as nulls, which may cause some confusion and unwanted side effects. (This is not true for all databases, e.g. DB2 and SQL server correctly distinguish between empty and NULL string)

SELECT length('X') FROM dual;  -- returns 1
SELECT length('')  FROM dual;  -- returns null

Comparing nulls is a bit funny as well, because of the three valued logic.

-- A bit of PL/SQL
DECLARE
  answer VARCHAR2(100);  -- long enough
  left NUMBER := 1;
  right NUMBER := NULL;
BEGIN
  IF left = right THEN
    answer := 'Equal';
  ELSIF NOT (left = right) THEN
    answer := 'Not equal';
  ELSE
    answer := 'Third option';
  END IF;
  dbms_output.put_line(answer);
END;
/

DECLARE
  answer VARCHAR2(100);  -- long enough
  left NUMBER := 1;
  right NUMBER := NULL;
BEGIN
  IF left <> right THEN
    answer := 'Un-equal';
  ELSIF NOT (left <> right) THEN
    answer := 'Not un-equal';
  ELSE
    answer := 'Third option';
  END IF;
  dbms_output.put_line(answer);
END;
/

Both times, 'Third option' is written to the screen. This can get funny in triggers, when comparing the old and new value of a field, trying to detect a change.

-- Some more Oracle specific code:
-- (In some trigger)
IF :NEW.foo <> :OLD.foo THEN                 -- Won't fire when :NEW.foo or :OLD.foo is null

IF
  :OLD.foo IS NULL AND :NEW.foo IS NOT NULL  -- detect changes from null
  OR
  :OLD.foo IS NOT NULL AND :NEW.foo IS NULL  -- detect changes to null
  OR
  :OLD.foo <> :NEW.foo                       -- detect other changes
THEN

Also see http://www.sqlite.org/nulls.html

(NULL = NULL) results in NULL, not TRUE, which is sometimes what you want. Then again, sometimes it isn't. I often find myself writing:

(x = y) OR (x IS NULL AND y IS NULL) OR (x IS NOT NULL AND y IS NOT NULL)

[SQL Server has a "checksum" function that returns the same value for any null. This allows the following:

CHECKSUM(x) = CHECKSUM(y)

which will work even when either x or y or both are null.] Now, what they really should provide is some syntax, e.g. x == y which would mean the same as above. If you had this syntax, you wouldn't need "IS [NOT] NULL" anymore, you could just do

x == NULL or x !== NULL

-- SimonKissane

CategoryNull