title: How to select rows, and nearby rows
body: <p><a href="http://sqlfiddle.com/#!18/5bdce/1" rel="noreferrer"><strong>SQL Fiddle</strong></a></p>
  
  <h2>Background</h2>
  
  <p>I have a table of values that some need attention:</p>
  
  <pre><code>| ID      | AddedDate   |
  |---------|-------------|
  |       1 | 2010-04-01  |
  |       2 | 2010-04-01  |
  |       3 | 2010-04-02  |
  |       4 | 2010-04-02  |
  |       5 | NULL        | &lt;----------- needs attention
  |       6 | 2010-04-02  |
  |       7 | 2010-04-03  |
  |       8 | 2010-04-04  |
  |       9 | 2010-04-04  |
  | 2432659 | 2016-06-15  |
  | 2432650 | 2016-06-16  |
  | 2432651 | 2016-06-17  |
  | 2432672 | 2016-06-18  |
  | 2432673 | NULL        | &lt;----------- needs attention
  | 2432674 | 2016-06-20  |
  | 2432685 | 2016-06-21  |
  </code></pre>
  
  <p>I want to select the rows where <code>AddedDate</code> is null, and i want to select rows around it. In this example question it would be sufficient to say rows where the <code>ID</code> is ±3. This means i want:</p>
  
  <pre><code>| ID      | AddedDate   |
  |---------|-------------|
  |       2 | 2010-04-01  | ─╮
  |       3 | 2010-04-02  |  │
  |       4 | 2010-04-02  |  │
  |       5 | NULL        |  ├──ID values ±3
  |       6 | 2010-04-02  |  │
  |       7 | 2010-04-03  |  │
  |       8 | 2010-04-04  | ─╯
  
  | 2432672 | 2016-06-18  | ─╮
  | 2432673 | NULL        |  ├──ID values ±3
  | 2432674 | 2016-06-20  | ─╯
  </code></pre>
  
  <blockquote>
    <p><strong>Note</strong>: In reality it's a table of 9M rows, and 15k need attention.</p>
  </blockquote>
  
  <h2>Attempts</h2>
  
  <p>First i create a query that builds the ranges i'm interested in returning:</p>
  
  <pre><code>SELECT
    ID-3 AS [Low ID],
    ID+3 AS [High ID]
  FROM Items
  WHERE AddedDate IS NULL
  
  Low ID   High ID
  -------  -------
  2        8 
  2432670  2432676
  </code></pre>
  
  <p>So my initial attempt to use this does work:</p>
  
  <pre><code>WITH dt AS (
     SELECT ID-3 AS Low, ID+3 AS High
     FROM Items
     WHERE AddedDate IS NULL
  )
  SELECT * FROM Items
  WHERE EXISTS(
      SELECT 1 FROM dt
      WHERE Items.ID BETWEEN dt.Low AND dt.High)
  </code></pre>
  
  <p>But when i try it on real data:</p>
  
  <ul>
  <li>9 million total rows</li>
  <li>15,000 <em>interesting</em> rows</li>
  <li>subtree cost of 63,318,400</li>
  <li>it takes hours (before i give up and cancel it)</li>
  </ul>
  
  <p><a href="https://i.stack.imgur.com/2YfPW.png" rel="noreferrer"><img src="https://i.stack.imgur.com/2YfPW.png" alt="enter image description here"></a></p>
  
  <p>There's probably a more efficient way.</p>
  
  <h2>Bonus Reading</h2>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/1813641/select-a-row-and-rows-around-it">Select a row and rows around it</a></li>
  <li><a href="https://stackoverflow.com/questions/15101457/select-rows-with-matching-columns-from-sql-server">Select Rows with matching columns from SQL Server</a></li>
  <li><a href="https://stackoverflow.com/questions/9320485/how-can-i-search-for-rows-around-a-given-string-value">How can I search for rows &quot;around&quot; a given string value?</a></li>
  <li><a href="https://stackoverflow.com/questions/758186/how-to-get-n-rows-starting-from-row-m-from-sorted-table-in-t-sql">How to get N rows starting from row M from sorted table in T-SQL</a></li>
  </ul>