By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

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

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy