NULLs in comparisons

<< Click to Display Table of Contents >>

Navigation:  Notes on Local SQL >

NULLs in comparisons

Previous pageReturn to chapter overviewNext page
hmtoggle_plus1See also

Be aware, that Paradox and dBase via BDE treats NULLs in comparisons in a way different from many other database systems.

Comparisons

In comparing e.g. Table2 versus Table1
Table1Table2NULLs
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