Friday, March 28, 2008

NULL should be renamed to UNKNOWN

problem:

the following has no output when it should has:

select * from src where n not in (select n from dst);



reproduce the problem:


create table src (n int);


create table dst(n int);


insert into src(n) values(1),(2),(3),(4),(5);
insert into dst(n) values(1),(2),(3);
select * from src where n not in (select n from dst); -- lists 4, 5

insert into dst(n) values(null);
select * from src where n not in (select n from dst); -- no output


should list 4 and 5?


it isn't, it didn't list anything


hmm.. seems strange



explanation:



SELECT 'SEE' WHERE 7 IN (SELECT 2 UNION SELECT 4)
is a shorthand for:
SELECT 'SEE' WHERE 7 = 2 OR 7 = 4



SELECT 'SEE' WHERE 7 NOT IN (SELECT 2 UNION SELECT 4)
is a shorthand for:
SELECT 'SEE' WHERE 7 <> 2 AND 7 <> 4




now let's inject NULL data :

SELECT 'SEE' WHERE 7 IN (SELECT 2 UNION SELECT 4 UNION SELECT NULL)
is a shorthand for:
SELECT 'SEE' WHERE 7 = 2 OR 7 = 4 or 7 = NULL



SELECT 'SEE' WHERE 7 NOT IN (SELECT 2 UNION SELECT 4 UNION SELECT NULL)
is a shorthand for:
SELECT 'SEE' WHERE 7 <> 2 AND 7 <> 4 AND 7 <> NULL



you cannot compare NULL, null means unknown. by the virtue of it being unknown, the expression 7 <> NULL is neither true nor false. the expression 7 = NULL, is also neither true nor false. 7 <> NULL: NULL, 7 = NULL: NULL.

100 <> 500 results true
100 = 500 results false
100 <> NULL results NULL
100 = NULL results NULL
100 IS NULL results false
100 IS NOT NULL results true
NULL = NULL results NULL
NULL <> NULL results NULL

NULL AND NULL results NULL
NULL AND TRUE results NULL
TRUE AND NULL results NULL
TRUE AND TRUE results TRUE

NULL OR NULL results NULL
NULL OR TRUE results TRUE
TRUE OR NULL results TRUE
TRUE OR TRUE results TRUE


NULL represents unknown, cannot be determined, not yet determined

rough analogy, think of WHERE A <> B as left hand <> right hand

WHERE 100 <> NULL

in plain english: is the left hand holding 100 dollar bill different with the bill in the closed right hand?

so what's the answer for 100 <> NULL?


let's have an example, your friend approach you, his/her left hand is open, you can see 100 bucks, right hand is close, you can't see anything. then he/she asks you if right hand holds different bill than the left hand, what would you(or any human) would answer? a human would answer, "it's different", "not different", or could answer "i don't know"


while on a computer, when you ask it, the computer's answer is always "i don't know." there is no gratification for a computer to guess correctly. perhaps, it's human nature to try to predict unknown forces, like trying to gauge if himself is capable of ESP, of capable of correctly predicting things 9 out of 10, or inclined in believing in divination(god will help me correctly guess so i can win this Deal or No Deal). humans are constantly feeding on gratifications, be it a guessing game, or trying their "pure luck"(believing in divination sometimes plays a large role here). so much for digressing :-)



so there we go, computers can't guess, it always answer "i don't know" when faced with unknown.


so the answer for the expression WHERE 100 <> NULL is unknown. neither true nor false. 100 = NULL is also unknown, neither true nor false. perhaps, RDBMS vendors should rename NULL to UNKNOWN.


let's try replacing NULL with UNKNOWN

100 <> 500 results true
100 = 500 results false
100 <> UNKNOWN results UNKNOWN
100 = UNKNOWN results UNKNOWN
100 IS UNKNOWN results false
100 IS NOT UNKNOWN results true
UNKNOWN = UNKNOWN results UNKNOWN
UNKNOWN <> UNKNOWN results UNKNOWN

UNKNOWN AND UNKNOWN results UNKNOWN
UNKNOWN AND TRUE results UNKNOWN
TRUE AND UNKNOWN results UNKNOWN
TRUE AND TRUE results TRUE


UNKNOWN OR UNKNOWN results UNKNOWN
UNKNOWN OR TRUE results TRUE
TRUE OR UNKNOWN results TRUE
TRUE OR TRUE results TRUE


there it is, more intuitive, perhaps when reading NULL, mentally read them as UNKNOWN.


these would not return a row:
SELECT * FROM employee WHERE FALSE
SELECT * FROM employee WHERE NULL

this could:
SELECT * FROM employe WHERE TRUE




let's go back on NOT IN construct:


SELECT 'SEE' WHERE 7 NOT IN (SELECT 2 UNION SELECT 4 UNION SELECT NULL)
is a shorthand for:
SELECT 'SEE' WHERE 7 <> 2 AND 7 <> 4 AND 7 <> NULL

resolved to:
SELECT 'SEE' WHERE TRUE AND TRUE AND NULL

then resolved to:
SELECT 'SEE' WHERE NULL


final result: no output


perhaps the rationale for using the word NULL is because it sounds so computer-sciencey. as for not using UNKNOWN as an identifier for er.. unknown, heck, it's hard to read double negatives, NOT UNKNOWN, oh.. humour me :-)


but... all these arguments can be thrown out of the window when mainstream programming languages like c# go the extra mile to make null comparisons not much to fuzz about:


int? a = 7;

int? b = null;


if (a != null) Console.WriteLine("indeed");

if (a != b) Console.WriteLine("still indeed");



programmer's life is sometimes difficult, impedance mismatches on our array of tools are in your face. our tools sometimes deviates from what we expected and asserted them to be. nonetheless we're constantly humbly learning to deal and get over with it.

which led me to the conclusion that i shouldn't have written these IS NULL, IS NOT NULL stuffs in the first place :-)



// Buen


Einstein argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer.
- Fred Brooks, Jr.



P.S.

i'd go on a limb here to generalize VB.NET's inconsistency(for the nth time), it's the MySQL of imperative programming languages. null handling in vb.net is basically broken. but perhaps one of VB.NET's few redeeming factor is its block-structured constructs, e.g. If-EndIf, Select-EndSelect, For-Next, Do-Loop, etc, they are very programmer-friendly, i wish C# use those block delineators instead of curly brackets



string s = null;
if (s == "") MessageBox.Show("Not Good"); else MessageBox.Show("Good");
// outputs Good

Dim s As String
s = Nothing
If s = "" Then
MessageBox.Show("Broken")
Else
MessageBox.Show("Expect Nothing")
End If
' outputs Broken

19 Comments:

Anonymous Anonymous said...

Hello
http://www.rhythmmethodlive.com/ - order lexapro
Another good fact about this Lexapro drug is that its impact is of short duration and one will get highly energetic after using it.
[url=http://www.rhythmmethodlive.com/]lexapro for sale[/url]
Hence it is completely a reliable drug to use out.
discount lexapro
Another good fact about this Lexapro drug is that its impact is of short duration and one will get highly energetic after using it.

7:17 AM

 
Anonymous Anonymous said...

Hi,
http://www.hammondneighbors.com/ - buy klonopin online
If you are someone who lives daily with anxiety and is seeking to end this menace of the mind then look no further than this article.
[url=http://www.hammondneighbors.com/]klonopin pharmacy[/url]
The active chemical in Klonopin binds itself to the brains GABA receptor, and quickly provides relief.
generic klonopin
Klonopin is meant to be a short term anxiety solution(2-4 months), as continued use can lead to dependency.

5:14 AM

 
Anonymous Anonymous said...

Hi,
buy phentermine
The appetite-reducing effect decreases in a few weeks therefore the use of these medicines is only during the first few weeks of the weight loss program.
[url=http://www.fourseasonsindiatour.com/]phentermine 37.5 mg[/url]
If these side effects occur, stop the use of adipex and inform the doctor right away.
http://www.fourseasonsindiatour.com/ - phentermine sale

Adipex with a generic name of phentermine is a sympathomimetic appetite suppressant.

8:00 AM

 
Blogger julia f. Baca said...

buy viagra

viagra online

generic viagra

11:51 PM

 
Anonymous Anonymous said...

iphone 4 unlock
how to unlock iphone 4
unlock iphone 4

http://mitchalbom.com/d/news/7079/immediate-release-ernie-play-open-april http://www.huwy.eu/ie/result/file-sharing-recommendations
I've asked this question on Yahoo! Answers before, but it did not work out at all. My computer started freezing out of random, so I rebooted my computer entirely. But after the reboot, it still does it. I have only 12 programs installed, which are: -Adobe Fireworks CS4 (NEEDED) -Adobe Flash Player Plug-in (For Mozilla Firefox) -Apple Application Support -Apple Software Update -AVG Free 9.0 -Belkin Wireless Utility (My Internet) -Camtasia Studio (NEEDED) -Free Registry Repair (Tried To Fix With This) -Google Chrome (NEEDED) -Java(TM) 6 Update 17 -Microsoft Visual C++ 2005 Redistributable -Mozilla Firefox 3.5.6 (NEEDED) -Quicktime Pro 7 (NEEDED) -Registry Easy v5.6 Pro (Tried To Fix With This) -Windows Explorer 8 -Windows XP Service Pack 3 -WinRAR Archiver Pro (NEEDED) My Computer is 10 Years Old. A Compaq FS740 Monitor with a Compaq Presario Tower. I have different problems every time I HAVE to reboot. I Full-Scanned the computer with my AVG Free 9.0, and no viruses were found. I don't know if it's either a problem with the Registry, a Virus, or something else. What can I do to stop this random freezing?
unlock iphone 4 iphone 4 unlock

unlock iphone 4 [url=http://unlockiphone44.com]iphone 4 unlock[/url] unlock iphone 4 iphone 4 unlock

8:50 PM

 
Anonymous Anonymous said...

best foreign language software http://buyoem.co.uk/fr/product-37199/Magic-Music-Workshop-8-0 bidpro schedule software [url=http://buyoem.co.uk/de/category-10/Internet?page=5]shopping cart software inventory[/url] get software peoplepc
[url=http://buyoem.co.uk/product-33766/Autodesk-AutoCAD-2012-x32]Autodesk AutoCAD 2012 x32 - Software Store[/url] best math tutor software
[url=http://buyoem.co.uk/de/product-31553/ShareTool-1-2-MAC][img]http://buyoem.co.uk/image/5.gif[/img][/url]

4:40 AM

 
Anonymous Anonymous said...

best rated telephone answering software http://buyoemsoftware.co.uk/product-10579/NCSS-PASS-GESS-Statistical-And-Data-Analysis-v2007 accouning software [url=http://buyoemsoftware.co.uk/product-36872/Okdo-Document-Converter-Professional-4-3]meta-analysis stat d software package[/url] weight goal software
[url=http://buyoemsoftware.co.uk/product-31541/Adobe-Lightroom-2-5-MAC]Adobe Lightroom 2.5 MAC - Download OEM, Software Sale, OEM Software[/url] silhouette software for plasma cutters
[url=http://buyoemsoftware.co.uk/es/product-36919/WaterProof-PHPEdit-4-1][img]http://buyoem.co.uk/image/3.gif[/img][/url]

2:39 AM

 
Anonymous Anonymous said...

click RwJQmdRs [URL=http://www.hermes-birkinprice.weebly.com/]hermes replica[/URL] with low price XjwaPFXt [URL=http://www.hermes-birkinprice.weebly.com/ ] http://www.hermes-birkinprice.weebly.com/ [/URL]

6:57 PM

 
Anonymous Anonymous said...

A huge dick in my pussy,the warm wet tounge up my arse and cum along with pussy
juice all over me. Fuck, ozzy

Have a look at my blog post; hcg injections

4:44 AM

 
Anonymous Anonymous said...

Noгmallу I dο not rеad post on blogs, however ӏ wish to sаy that this
write-up vеry fоrced me to check out and do ѕo!

Youг writing style has been ѕurρrіseԁ
mе. Thanks, νeгy nіce post.

Feеl freе to vіsit my homepage; steroids wikipedia

6:39 AM

 
Anonymous Anonymous said...

[url=http://redbrickstore.co.uk/products/zyprexa.htm][img]http://onlinemedistore.com/3.jpg[/img][/url]
steroid pharmacy online http://redbrickstore.co.uk/products/betnovate.htm pharmacy techinician online course [url=http://redbrickstore.co.uk/products/innopran-xl.htm]percodan online pharmacy[/url]
pharmacy school prerequisits http://redbrickstore.co.uk/products/cefixime.htm madsion avenue pharmacy [url=http://redbrickstore.co.uk/products/lopid.htm]lopid[/url]
thrifty pharmacy new york http://redbrickstore.co.uk/products/alavert.htm tricare for life pharmacy formulary and non formulary [url=http://redbrickstore.co.uk/products/prandin.htm]what to do after u get a pharmacy degree[/url]
ndsu graduating class pharmacy 1947 http://redbrickstore.co.uk/categories/hypnotherapy.htm nutri chem pharmacy [url=http://redbrickstore.co.uk/products/viramune.htm]viramune[/url]

7:21 AM

 
Anonymous Anonymous said...

You really make it seem so easy with your presentation but I find this matter to be
really something which I think I would never understand.
It seems too complicated and very broad for me. I am looking forward for your next post, I'll try to get the hang of it!

Stop by my weblog ... books of ra kostenlos spielen

3:51 PM

 
Anonymous Anonymous said...

Hello Dear, are you genuinely visiting this websіte regulаrly, if so then you will absοlutely gеt nice knowledgе.


Here іs my homepage: side effects of hcg diet
My website: hcg diet

7:22 PM

 
Anonymous Anonymous said...

Thank you for the auspicious writeup. It in fact was a amusement account it.
Look advanced to far added agreeable from you! By the way,
how could we communicate?

my blog post; http://somosconservadores.ning.com/

8:18 PM

 
Anonymous Anonymous said...

I really like what you guys are usually up
too. This type of clever work and coverage! Keep up the fantastic works guys I've included you guys to our blogroll.

Feel free to visit my web-site book of ra kostenlos downloaden

11:15 PM

 
Anonymous Anonymous said...

I was recommended this website by my cousin. I am not sure whether this post is written by
him as no one else know such detailed about my difficulty.
You are amazing! Thanks!

my blog ... fayeswildlifefotos.blogspot.com

4:25 AM

 
Anonymous Anonymous said...

Can you tell us more about this? I'd want to find out more details.

Have a look at my page: realsizegeneticsreview.com

12:29 PM

 
Anonymous Anonymous said...

What's up mates, good paragraph and good arguments commented here, I am truly enjoying by these.

My page: background check

6:50 PM

 
Anonymous my suggestion said...

this is so nice .

4:11 AM

 

Post a Comment

Links to this post:

Create a Link

<< Home