Using Not IN SQL Server

Question

I have a query in which I'm trying to find how many customers from one database exist in another.

select customerid 
from transactions 
where dtcreated > '5/2/14'  in (Select customerid 
                                from database2.dbo.customers 
                                where dtcreated > '3/1/14')
Group by customerid

This query works just fine. But obviously, some customers did not have transactions in the time period I'm searching for in the transactions. I'm trying to find those customer ID's.

I've tried:

Select customerid 
from database2.dbo.customers 
where dtcreated > '3/1/14' NOT IN (select customerid 
                                   from transactions 
                                   where dtcreated > '5/2/14')
group by customerid

But this query doesn't return any results.

where Cast(dtcreated as datetime) >Cast('3/1/14' as datetime) — Mihai, May 01 '15 at 18:24

score 2 · Accepted Answer · answered May 01 '15 at 18:36

I think you are not getting result because of the fact that there is a NULL value in the customer id column.

 Select distinct customerid 
    from database2.dbo.customers 
    where dtcreated> '2014-01-03' and 
    customerid NOT IN 
              (
                 select customerid 
                 from transactions 
                 where dtcreated > '2014-02-05' and 
                 trim(customerid) is not null
              )

score 0 · Answer 2 · answered May 01 '15 at 18:15

0

Neither query should work as you have them right now. You haven't specified which field is in/not in the sub-query. Modify it to something like this

Select c.customerid from database2.dbo.customers c where c.dtcreated > '3/1/14' and c.customerid NOT  in
    (

    select customerid from transactions where created > '5/2/14'
    )
    group by c.customerid

answered May 01 '15 at 18:15

JP.

5,536
7
58
100

Same problem. No results. – Shmewnix May 01 '15 at 18:17
When you run your subquery by itself do you get results? Also, can you use datetime variables instead of strings? – JP. May 01 '15 at 18:18
I could, but the dtcreated column is of type dtcreated, so that shouldn't matter. – Shmewnix May 01 '15 at 18:29

orgtigger · Answer 3 · 2015-05-01T18:43:13.710

If your customer ids are the same, you can do an inner join on the table and then just filter down to the result set you want. Should be a better way to achieve the intersection you are asking for.

select database2.dbo.customers.customerid
from transactions trans
   INNER join database2.dbo.customers
      on trans.customerid = database2.dbo.customers.customerid
where trans.dtcreated < '5/2/14' 
   and database2.dbo.customers.dtcreated > '3/1/14'

Group by database2.dbo.customers.customerid

Apparently there is a keyword for this type of operation

SELECT customerid
FROM transactions
WHERE dtcreated < '5/2/14' 

INTERSECT

SELECT customerid
FROM database2.dbo.customers
WHERE dtcreated > '3/1/14'

You should add *at least* two or three sentences to *explain* what you're suggesting, and why ... — marc_s, May 01 '15 at 18:30

score 0 · Answer 4 · answered May 01 '15 at 18:28

I think Problem is Due to date formats use this:

Select customerid 
from database2.dbo.customers 
where CAST(dtcreated  as DATE) > '2014-01-03' NOT IN (select customerid 
                                   from transactions 
                                   where CAST(dtcreated  as DATE) > '2014-02-05')
group by customerid

score 0 · Answer 5 · edited May 23 '17 at 11:58

I think you need to correct the syntax for both of your queries. Based on what I am interpreting that you are trying do:

Select customerid 
from database2.dbo.customers 
where dtcreated > '3/1/14' 
and customerid NOT IN (select customerid 
                       from transactions 
                       where dtcreated > '5/2/14')

Whichever field you return in your subquery should return the field you are saying 'not in' for. In addition since you are only returning customer ids, you don't need the group by at the end. This will return customers a dbo.customers recorded with a date later than '3/1/14' that are not in the transactions table with a date greater than '5/2/14'. I hope this is what you were looking for.

Another alternative that might be useful for you is 'not exists' Not Exists sometime has better performance. for more details see: What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

this would look like :

Select customerid 
from database2.dbo.customers c
where dtcreated > '3/1/14' 
and not exists(select *                           
               from transactions t
               where dtcreated > '5/2/14'
               and c.customerid  = t.customerid
               )

Using Not IN SQL Server

5 Answers5