>Regarding the query below, the value "A&P" is not in the acctnrlu table (in the luacctnm column). A&P is in curdata. So the query should return rows. However, it wasn't returning anything. There was one row with a null in acctnrlu.luacctnm. I didn't think this should matter. Yet when I removed the row with the null, the query began working. Is this the expected behavior?
>
>
>SELECT accountnam FROM curdata WHERE accountnam NOT in (SELECT luacctnm FROM acctnrlu)

IN it is a multiple OR equal comparison
NOT IN it is a multiple AND not equal comparison

comparing with NULL return False
OR ignore False if a true exists
AND become False when a False exists

-- written on T-SQL
SELECT 1 WHERE 5 in (5,null)
-- THIS IS EQUAL TO
SELECT 1 WHERE 5 =5 OR 5=null

SELECT 2 WHERE 4 not in (5,null)
-- THIS IS EQUAL TO
SELECT 2 WHERE NOT (4=5 OR 4=null)
-- EQUAL TO 
SELECT 2 WHERE 4<>5 AND 4<>null
-- !!! NOT EQUAL TO 
SELECT 2 WHERE 4<>5 AND 4 IS NOT NULL

Moreover, if 5 is in the list, it is certain that 5 there is,
but if a NULL(that is an unknown value) is listed,
then 4 is not certain that there is not.

You can to use NOT EXISTS().

Many developers feel that NOT EXISTS(). and NOT IN() they are equivalent,
but not at all

SELECT 3 WHERE NULL IN (NULL)

SELECT 3 WHERE NULL NOT IN (NULL)
-- NOT EQUAL TO
SELECT 3 WHERE NOT EXISTS(SELECT * WHERE NULL=NULL)

You can uses NOT IN() with a not null items list, like a primary key.