title: TSQL: NULL in the "IN" clause
body: <p>I have a need to filter records where:</p>
  
  <pre><code>NotificationRead = 0 || NULL --&gt; IF GetRead = 0
  NotificationRead = 1 --&gt; IF GetRead = 1
  NotificationRead = 0 || 1 || NULL --&gt; IF GetRead = NULL
  </code></pre>
  
  <p>Here is the query I am using for this:</p>
  
  <pre><code>DECLARE @GetRead BIT
  DECLARE @Query VARCHAR(20)
  
  SET @GetRead = NULL
  
  IF @GetRead = 0 SET @Query = '0,NULL'
  ELSE IF @GetRead = 1 SET @Query = '1'
  ELSE SET @Query = '0,1,NULL'
  
  SELECT * FROM vwNotifications  WHERE NotificationRead IN (@Query)
  </code></pre>
  
  <p>The query above basically fails when I supply <code>NULL</code> in the <code>IN</code> clause.
  I do know the reason why thanks to <a href="https://stackoverflow.com/questions/6362112/in-clause-with-null-or-is-null">this question</a>.</p>
  
  <p>But if I take an approach as suggested in that question's answer (using <code>NotificationRead IN (@Query) OR NotificationRead IS NULL</code>), I get all records where <code>NotificationRead = NULL</code> when I don't need them for example, when <code>@GetRead = 1</code></p>
  
  <p>Can you please point me to the right direction here?</p>