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