title: SQL count case else 0 vs null
body: <p>I really need an explination for this. Say you have the following table:</p>
  
  <pre><code>    Columns:
  id  int(11) AI PK
  user_id int(11)
  is_complete int(11)
  </code></pre>
  
  <p>In the above table <code>is_complete</code> can either be <code>1</code> or <code>0</code></p>
  
  <p>Now you wish to do some counting so you create the following <code>SQL</code> statement:</p>
  
  <pre><code>COUNT(CASE
      WHEN is_complete = 0 THEN 1
      ELSE 0
  END) AS not_complete
  </code></pre>
  
  <p>Say the result of this would be:</p>
  
  <p><a href="https://i.stack.imgur.com/EZIoW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EZIoW.png" alt="enter image description here"></a></p>
  
  <p>(Which is incorrect)</p>
  
  <p>Now you change the exact same statement to:</p>
  
  <pre><code>COUNT(CASE
      WHEN is_complete = 0 THEN 1
      ELSE null
  END) AS not_complete
  </code></pre>
  
  <p><em>Note ive changed <code>0</code> to <code>null</code></em></p>
  
  <p>And you get the following result:</p>
  
  <p><a href="https://i.stack.imgur.com/40uy4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/40uy4.png" alt="enter image description here"></a></p>
  
  <p>After some intense testing you will find that the 1. incorrect statement actually counts all no matter what. </p>
  
  <p>My question is why? </p>