SQL & how different databases implement it

Some vaguely structured thoughts on the differences that you see between SQL as implemented by one DBMS and that of another.

17 September 2006

 

More on empty strings as NULLs

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 <> '';

Comments: Post a Comment

Subscribe to Post Comments [Atom]





<< Home

Links

Archives

September 2005   October 2005   December 2005   January 2006   March 2006   May 2006   June 2006   September 2006   May 2007   June 2007   November 2007   December 2007   March 2008   April 2008   December 2008   January 2011  

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]