title: Difference between NULL in SQL and null in programming languages
body: <p>I've just come across an interesting scenario on how NULL is handled in T-SQL (and possibly other forms of SQL). The issue is pretty well described and answered by <a href="https://stackoverflow.com/questions/2866714/how-does-ansi-nulls-work-in-tsql">this question</a> and I've illustrated the issue below;</p>
  
  <pre><code>-- SET ANSI_NULLS ON -- Toggle this between ON/OFF to see how it changes behaviour
  DECLARE @VAR1 DATETIME 
  DECLARE @VAR2 DATETIME 
  
  SET @VAR1 = (SELECT CURRENT_TIMESTAMP)
  SET @VAR2 = (SELECT NULL)
  
  -- This will return 1 when ansi_nulls is off and nothing when ansi_nulls is on
  SELECT 1 WHERE @VAR1 != @VAR2
  
  DECLARE @TstTable TABLE (
     COL1 DATETIME,
     COL2 DATETIME)
  
  INSERT INTO @TstTable
  SELECT @VAR1, @VAR1
  UNION 
  SELECT @VAR1, NULL
  
  -- This won't ever return a value irrespective of the ansi_nulls setting
  SELECT * FROM @TstTable WHERE COL1 != COL2
  </code></pre>
  
  <p>This situation led me to question my understanding of null representations specifically within SQL. I've always understood null to mean that it has no value. This seems to be an incorrect assumption given the first paragraph of <a href="http://msdn.microsoft.com/en-us/library/aa196106%28v=sql.80%29.aspx" rel="nofollow noreferrer">this page</a>. It states (my emphasis...I could quite easily just highlight the whole paragraph though);</p>
  
  <blockquote>
    <p>A value of NULL indicates the value is <strong>unknown</strong>. A value of NULL is
    different from an empty or zero value. No two null values are equal.
    Comparisons between two null values, or between a NULL and any other
    value, return unknown because the value of each NULL is unknown.</p>
  </blockquote>
  
  <p>Does this hold true for T-SQL variable conditions also? It certainly does for my <code>SELECT 1 WHERE @VAR1 != @VAR2</code> example above, but I don't understand why NULL in this instance is considered "UNKNOWN" and not empty/uninitialised/nothing etc. I know ANSI_NULLS changes how this works, but it is deprecated and will be removed from some future version.</p>
  
  <p>Can someone offer a good explanation as to why NULL in T-SQL refers to an <strong>unknown</strong> value rather than an <strong>uninitialised</strong> value? If so, can you extend your answer to show why T-SQL variables with a NULL value are also considered to be unknown? </p>