Q

NULL logic

I'm trying to determine the standard SQL interpretation when dealing with a NULL comparison. For example, assume table t1 allows NULLs in columns x and y:

INSERT INTO t1(x,y) VALUES(NULL,NULL);
DELETE FROM t1 WHERE x=y;

Since standard SQL uses tri-state logic when comparing NULLs, does the DELETE take place or not? My understanding is that the expression x=y when x or y are NULL produces a NULL, not a true or a false. So, in this example, wouldn't it be as if the DELETE statement were re-written as:

DELETE FROM t1 where NULL;

What does the SQL standard say?


Yes, you're right, NULL doesn't equate to anything, not even another NULL. The example you give will evaluate as

DELETE FROM t1 where UNKNOWN;

So you cannot delete the rows that way. You'll have to use the IS NULL syntax --

delete
  from t1 
 where x is null
   and y is null

For detailed examples of SQL three-valued logic involving NULLs, see SQL: Defining and Visualizing NULLs.

For More Information


This was first published in June 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close