Difference in results between case syntax I have been playing around with CASE syntax and noticed the following difference in behaviour which I don't full understand. 

Given the below query

DECLARE @Value1 INT 

SELECT 
    'Col1' = CASE WHEN @Value1 IS NULL THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Col2' = CASE @Value1 WHEN NULL THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Val' = @Value1


The result set I am getting is this:


  Col1 | Col2 | Val
  
  Yay | Oh | NULL


I would have thought they would return the same result. Would anyone be happy to explain what's going here?
For the same reason Col3 I've added below returns "Oh". In your Col2 you are testing if NULL equals @Value1. NULL is undefined, it doesn't equal anything. As Codd would say 


  Rule 3: Systematic treatment of null values:
  
  The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.


DECLARE @Value1 INT 

SELECT 
    'Col1' = CASE WHEN @Value1 IS NULL THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Col2' = CASE @Value1 WHEN NULL THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Col3' = CASE WHEN NULL = NULL  THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Val' = @Value1