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