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
- Dozens more answers to tough SQL questions from Rudy Limeback are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in June 2002