Handling NULL in SQL Server
Have a look at remarks on MS Docs about IS NULL:


  Remarks
  To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=).
  Comparison operators return UNKNOWN when either or both arguments are NULL. 


And at SET ANSI_NULLS MS Docs:


  SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.  


Let me add some other records to your table:

INSERT INTO Test_Table 
VALUES (NULL, 'A'),(NULL,'B'),(NULL,'C'),(NULL,'D'),(1,'E'),(2,'F');


Now, your query:




SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=id;



Returns:



id | pName
-: | :----
 1 | E    
 2 | F    



And the same for your second query:




Declare @ID INT = NULL

SET ANSI_NULLS OFF
SELECT CASE WHEN @ID &gt; 0 THEN @ID ELSE id END AS Statements, * 
FROM   Test_Table  
WHERE  ID = CASE WHEN @ID &gt; 0 THEN @ID ELSE ID END
GO



Statements | id | pName
---------: | -: | :----
         1 |  1 | E    
         2 |  2 | F    



Null indicates lack of value and you must explicitly check for NULL values using IS NULL:

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id IS NULL;


dbfiddle here