understanding an sql query result

[Bob Weiner]

I have been troubleshooting an SQL query and it turns out that the problem
is my lack of understanding. Hopefully, someone here can explain.
Basically, I have two tables each containing a single field called 'letter'.
I have the following:

Table A: {a, b, c, d, NULL, a}
Table B: {c, d, e, f}

The following two queries generate expected results:

1. SELECT A.letter FROM A WHERE A.letter IN (SELECT B.letter FROM B)
Result: {c, d}

2. SELECT A.letter FROM A WHERE A.letter NOT IN (SELECT B.letter FROM B)
Result: {a, b, a}

Then I add a NULL value to Table B (B = {c, d, e, f, NULL}) and rerun the
same queries.

1. SELECT A.letter FROM A WHERE A.letter IN (SELECT B.letter FROM B)
Result: {c, d}

2. SELECT A.letter FROM A WHERE A.letter NOT IN (SELECT B.letter FROM B)
Result: {} (empty set)

Why does query 2 return different results when Table B contains a NULL?
MySql returns the same results.

Thanks
bob

 

[John Spencer]

SELECT A.letter FROM A WHERE A.letter NOT IN (SELECT B.letter FROM B)

Consider just the value Null for B.Letter. Since Null is treated as UNKNOWN
value you can never know if you have a match against it or not. So if

If you rewrote the above as
SELECT A.letter FROM A WHERE A.letter Not IN (NULL)

Is A.Letter Not In Null? The logic of the query engine says " I don't know
since I don't know the value of null"

So with the not in clause you would have to rewrite
SELECT A.letter FROM A WHERE A.letter NOT IN (SELECT B.letter FROM B WHERE
B.Letter is not null)

Or you could (I believe) write the query as
SELECT A.letter
FROM A LEFT JOIN B On A.Letter = B.Letter
WHERE B.Letter is Null

Since

 

[Bob Weiner]

Your answer makes sense but the logic doesn't seem to apply to the first IN
query. In that case, it reports the expected results - clearly deciding
which values from A are IN B and which are NOT. The difference between
query 1 and 2 does not demand more information from the database engine only
the complement of the result set.

Adding "B.letter is not null" does produce the desired result as you
suggested. I will use this to solve my problem but will not be entirely
confident with future queries of a similar nature.

Thanks!
bob