Using character data types in a sub-query

You may want to put this one in your notes to avoid hair loss: ____ table_1 Column name Type Nulls column_1 char(20) yes table_2 Column name Type Nulls column_1 char(20) yes >SELECT count(*) FROM table_1 WHERE table_1.column_1 NOT IN (SELECT column_1 from table_2); (count(*)) 0 1 row(s) retrieved. ___ Even though ___ >SELECT * FROM table_1 WHERE column_1 = "foo"; column_1 foo 1 row(s) retrieved. ___ and ___ >SELECT * FROM table_2 WHERE column_1 = "foo"; column_1 No rows found. ___ Even though the form is correct, the original query fails to produce believable results _if_ the results of the sub-query contain null values. I haven't confirmed it yet but this "quirk" appears to be a problem only with non-numeric data types. I was able to confirm that the problem still exists in 7.x. The fix for this is forcing your sub-query to exclude null values: ___ >SELECT count(*) FROM table_1 WHERE table_1.column_1 NOT IN (SELECT column_1 from table_2 WHERE column_1 IS NOT null); -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Doug Otto otto@alldata.com Alldata Corporation dotto@calweb.com Technical Manager, Database Development (800) 829-8727 ext. 3137