There is no difference.

It seems to me that there might be a difference when it comes to performance. Anyone care to elaborate on this?

All major engines (that is MySQL, SQL Server, Oracle and PostgreSQL) will merge these predicates on parsing stage, making identical plans from them.

Handling of these conditions is more complex that mere applying operators in one or another order.

For instance, in Oracle, an IS NOT NULL (or NOT IS NULL) condition implies a possibility to use an index, so a query like this:

SELECT  column
FROM    mytable
WHERE   column IS NOT NULL

will most probably be executed with an index fast full scan, with no additional checks made in runtime (since the NULL values just won't make it into the index, so it's no use to check them).

Even if each record would need to be checked, the order of checks will be defined by the optimizer (and not by the order the predicates and operators appear in the WHERE clause).

For instance, here is a plan for an Oracle query:

SQL> EXPLAIN PLAN FOR
  2  
  2  SELECT *
  3  FROM   t_test
  4  WHERE  NOT column IS NULL
  5  /

Explained

SQL> SELECT  *
  2  FROM    TABLE(DBMS_XPLAN.display())
  3  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 958699830
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    30 |  1260 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_TEST |    30 |  1260 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COLUMN" IS NOT NULL)

As you can see, the filter was translated internally into an IS NOT NULL (which Oracle along with most commenters seems to believe to be a more appropriate form)

Update:

As Jonathan Leffler pointed out, these is difference when evaluating tuples (as opposed to single columns).

A tuple consisting of mixed NULL and non-NULL values is neither a NULL nor a NOT NULL.

In PostgreSQL (which supports this predicate against tuples), both these expressions:

SELECT  (1, NULL) IS NULL
SELECT  (1, NULL) IS NOT NULL

evaluate to false.

Did you try:

CASE WHEN (ID IS NULL) THEN 'YES' ELSE 'NO' END AS ID_Value,

I only have access to 2008 right now, but I'd hope that this syntax would still work in 2005 (seems like something that would be part of the original definition of CASE).

You should use IS NOT NULL. (The comparison operators = and <> both give UNKNOWN with NULL on either side of the expression.)

SELECT * 
FROM table 
WHERE YourColumn IS NOT NULL;

Just for completeness I'll mention that in MySQL you can also negate the null safe equality operator but this is not standard SQL.

SELECT *
FROM table 
WHERE NOT (YourColumn <=> NULL);

Edited to reflect comments. It sounds like your table may not be in first normal form in which case changing the structure may make your task easier. A couple of other ways of doing it though...

SELECT val1 AS val
FROM  your_table
WHERE val1 IS NOT NULL
UNION ALL
SELECT val2 
FROM  your_table
WHERE val2 IS NOT NULL
/*And so on for all your columns*/

The disadvantage of the above is that it scans the table multiple times once for each column. That may possibly be avoided by the below but I haven't tested this in MySQL.

SELECT CASE idx
         WHEN 1 THEN val1
         WHEN 2 THEN val2
       END AS val
FROM   your_table
        /*CROSS JOIN*/
       JOIN (SELECT 1 AS idx
                   UNION ALL
                   SELECT 2) t
HAVING val IS NOT NULL  /*Can reference alias in Having in MySQL*/