<< Click to Display Table of Contents >> NULLs in comparisons |
Be aware, that Paradox and dBase via BDE treats NULLs in comparisons in a way different from many other database systems.
In comparing e.g. Table2 versus Table1
with the statement
SELECT col1
FROM Table2
WHERE col1 NOT IN
(SELECT col1
FROM Table1);
the BDE will return 3, where most other database systems will return NULL, an error message or just no result.
A more universal and portable approach will be to include a WHERE Col1 IS NOT NULL clause in the subquery like this
SELECT col1
FROM Table2
WHERE col1 NOT IN
(SELECT col1
FROM Table1
WHERE Col1 IS NOT NULL);
which is a more prudent solution avoiding the subquery NULL problem and will work equally well across various DBMSs.
Supplementary notes on LocalSQL added by Niels Knabe