SQL & how different databases implement it

A previous post showed how using an empty string '' in an INSERT statement gets treated as a NULL by Oracle.

But you should remember that this is also the same when using an empty string in a WHERE clause.

Look through the following script. Anywhere the WHERE clause includes the phrase col_v_nn <> '' no matter which column is being referred to the SELECT returns zero rows. This is because '' is being treated as NULL and of course a NULL is not equal to anything, nor is it not equal to anything. Similarly, the phrase col_v_nn = NULL of course is never true, neither is col_v_nn <> NULL.

All the above is true both for CHAR and VARCHAR2 columns and also for columns defined as NULL or NOT NULL.


DROP TABLE temp_one;

CREATE TABLE temp_one (
col_v varchar2(10),
col_v_nn varchar2(10) NOT NULL,
col_c char(10),
col_c_nn char(10) NOT NULL
) TABLESPACE ts_d;

INSERT INTO temp_one
VALUES ('aaa','aaa','aaa','aaa');

INSERT INTO temp_one
VALUES (NULL,'bbb',NULL,'bbb');

COMMIT;

SELECT COUNT(*) FROM temp_one
WHERE col_v_nn IS NOT NULL;

SELECT COUNT(*) FROM temp_one
WHERE col_v_nn <> '';

SELECT COUNT(*) FROM temp_one
WHERE col_v_nn <> NULL;

SELECT COUNT(*) FROM temp_one
WHERE col_v_nn = NULL;

SELECT COUNT(*) FROM temp_one
WHERE col_v_nn IS NOT NULL
AND col_v_nn <> '';



SELECT COUNT(*) FROM temp_one
WHERE col_v IS NOT NULL;

SELECT COUNT(*) FROM temp_one
WHERE col_v <> '';

SELECT COUNT(*) FROM temp_one
WHERE col_v <> NULL;

SELECT COUNT(*) FROM temp_one
WHERE col_v = NULL;

SELECT COUNT(*) FROM temp_one
WHERE col_v IS NOT NULL
AND col_v <> '';



SELECT COUNT(*) FROM temp_one
WHERE col_c IS NOT NULL;

SELECT COUNT(*) FROM temp_one
WHERE col_c <> '';

SELECT COUNT(*) FROM temp_one
WHERE col_c <> NULL;

SELECT COUNT(*) FROM temp_one
WHERE col_c = NULL;

SELECT COUNT(*) FROM temp_one
WHERE col_c IS NOT NULL
AND col_c <> '';



SELECT COUNT(*) FROM temp_one
WHERE col_c_nn IS NOT NULL;

SELECT COUNT(*) FROM temp_one
WHERE col_c_nn <> '';

SELECT COUNT(*) FROM temp_one
WHERE col_c_nn <> NULL;

SELECT COUNT(*) FROM temp_one
WHERE col_c_nn = NULL;

SELECT COUNT(*) FROM temp_one
WHERE col_c_nn IS NOT NULL
AND col_c_nn <> '';