I am trying to perform a basic NOT in query using Query Analyzer in SQL
2000. I get no results, and I know they are there.
select createdate,* from riccustomers where email  not in (select email
from ricorders)  order by custno desc

Why would this not work?




*** Sent via Developersdex http://www.developersdex.com ***

This problem usually has to do with SQL's treatment of NULLs. I'll use the
following U and V tables to demonstrate the problem:

CREATE TABLE U(col1 INT);
INSERT INTO U VALUES(2);
INSERT INTO U VALUES(7);
INSERT INTO U VALUES(9);

CREATE TABLE V(col1 INT);
INSERT INTO V VALUES(3);
INSERT INTO V VALUES(7);
INSERT INTO V VALUES(NULL);

Now try to answer which rows would be returned from U by the following
query:

SELECT * FROM U WHERE col1 NOT IN(SELECT col1 FROM V);

This query answers the question: return all members of U that are known not
to be in the list of members in V. 7 is known to be in V, so you don't want
to see it. However you cannot tell for sure whether 2 and 9 are not in V.
The NULL in V is an unknown value, which might be a 2 or might be a 9 or
might be another value. In order words, having a NULL in V, you can never
know for sure that a value in U is not in V. Hence you get an empty set
back. This was the logical explanation of why you get an empty set back from
the query. If you want a more technical explanation, evaluate the expression
for a value that is known to appear in both sets (e.g., 7), and one that isn't
(e.g., 2). Let's start with 7. The logical expression would be:

7 NOT IN(3, 7, NULL) =
NOT(7=3 OR 7=7 OR 7=NULL) =
NOT(FALSE OR TRUE OR UNKNOWN) =
NOT(TRUE) =
FALSE

Since the result is FALSE, we don't get 7 back. We know for sure that it
does appear in V, so we don't want to see it. Now evaluate the expression
for 2, which appears in U:

2 NOT IN(3, 7, NULL) =
NOT(2=3 OR 2=7 OR 2=NULL) =
NOT(FALSE OR FALSE OR UNKNOWN) =
NOT(UNKNOWN) =
UNKNOWN

Since the logical expression evaluated to UNKNOWN, and UNKNOWN in a filter
is treated like FALSE, you don't get 2 back. In other words, if there's a
NULL in V, values that appear in U and known to appear in V will yield a
FALSE, while other values in U will yield an UNKNOWN. You realize that you
will always get an empty set back.
If you want to return values from U that are not known to appear in V, that's
a different request which requires a different query. One option is to query
only the known list of values in V:

SELECT * FROM U WHERE col1 NOT IN
  (SELECT col1 FROM V WHERE col1 IS NOT NULL);

And you get 2 and 9 as expected. Another option is to use NOT EXISTS instead
of NOT IN as follows:

SELECT * FROM U WHERE NOT EXISTS
  (SELECT * FROM V WHERE V.col1 = U.col1);

This query also returns 2 and 9. The reason why you don't need to test for
known values explicitly here is that EXISTS can only return TRUE or FALSE.
It has no UNKNOWN state. Either the subquery returns rows or it doesn't. The
trick here is that an UNKNOWN state in the subquery's filter is treated like
FALSE, therefore the outer query yields the desired TRUE (NOT(FALSE)). You
get values that appear in U, but are not known to appear in V.

Show quoteHide quote

A common cause of this problem is that the subquery returns one or more NULL
values.  When this is the case, no rows will be returned.

Consider using NOT EXISTS instead:

SELECT o.createdate, c.*
FROM riccustomers c
WHERE NOT EXISTS
(
    SELECT *
    FROM ricorders o
    WHERE o.email = c.email
)
ORDER BY c.custno DESC

Show quoteHide quote

BTW, the query I previously posted won't work because of the reference to
the o.createdate column.  Assuming it is in the riccustomers table:

SELECT c.createdate, *
FROM riccustomers c
WHERE NOT EXISTS
(
    SELECT *
    FROM ricorders o
    WHERE o.email = c.email
)
ORDER BY c.custno DESC

Show quoteHide quote

One of the values for email in ricorders is NULL. To use an example:

1 IN (2, NULL) returns UNKNOWN, not FALSE, as the value of NULL is unknown
and might or might not be equal to 1. NOT (UNKNOWN) isn't TRUE, it's also
UNKNOWN. That's tri-valued Boolean logic for you :-)

Change your query to the follwoing to get the results you want:

SELECT createdate, <other columns>
FROM riccustomers cus
LEFT OUTER JOIN ricorders o
ON c.email = o.email
WHERE o.email IS NULL
ORDER BY sutno DESC

Show quoteHide quote

Thanks everyone!!

*** Sent via Developersdex http://www.developersdex.com ***

-- Try this

select createdate,* from riccustomers where email  not in (select email

from ricorders where email is not null)  order by custno desc

Hi Joey,

Welcome to the sqlserver newsgroups.

The code looks ok. Is this the actual complete select statement? I've found
that for me when the code is ok but it's not work the problem is in the
data. fyi, Another way of writing a set difference query is:

SELECT *
  FROM RicCustomers C
    LEFT JOIN RicOrders O
      ON C.email = O.email
  WHERE O.email IS NULL

depending on your foreign key constraints, you could change the left outer
join to a full outer join and find emails in either table that are not
present in the other.

Show quoteHide quote

3 Questions on db Theory/Design
Upgrade from SQL Server 7.0 to SQL Server 2000
restoring db without log
difference between inner join and 'where table1.id = table2.id
tips for db programming