I'm trying to determine the standard SQL interpretation when dealing with a NULL comparison. For example, assume...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.