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?


    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top