title: Select rows from table where value in column appears ones
body: <p>I have the following table with three fields as shown below:</p>
  
  <p>Table : <code>Testing</code></p>
  
  <pre><code>create table testing
  (
     colnum varchar(10),
     coldate date,
     colid int
  );
  </code></pre>
  
  <p>Insertion :</p>
  
  <pre><code>insert into testing values('111','2015-01-01',1);
  insert into testing values('111','2015-01-02',1);
  insert into testing values('111','2015-01-03',1);
  insert into testing values('111','2015-01-04',1);
  insert into testing values('111','2015-01-05',1);
  insert into testing values('222','2015-01-01',1);
  insert into testing values('333','2015-01-01',1);
  </code></pre>
  
  <p>Index creation:</p>
  
  <pre><code>create clustered index id_idx on testing(colid);
  create nonclustered index date_nidx on testing(coldate);
  create nonclustered index num_nidx on testing(colnum);
  </code></pre>
  
  <p><strong>Note</strong>: Now I want to display the records who is <strong>only in</strong> specific date and specific ID not the other date.</p>
  
  <p>For example: I want to display the records who is <strong>ONLY IN</strong> specified date and ID but not the other dates.</p>
  
  <p>Given Date: <code>2015-01-01</code><br>
  Given ID  : <code>1</code></p>
  
  <p>For which I have written following query:</p>
  
  <pre><code>select * from testing
  where coldate in ('2015-01-01')
        and coldid = 1
        and colnum not in(select colnum from testing where coldid = 1 and
                          coldate in('2015-01-02','2015-01-03','2015-01-04'
                               '2015-01-05');
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>colnum   coldate     colid
  --------------------------
  222     2015-01-01    1
  333     2015-01-01    1
  </code></pre>
  
  <p><strong>Explaination</strong>: The query shows two records because both records have only in specific date and id <strong>But</strong> the record <code>111</code> not shown because that also belongs to other dates as you can see in the above table.</p>
  
  <p>The above query works fine for me <strong>But</strong> taking more time for execution for billions of records. </p>