Talk:Null (SQL): Difference between revisions
assess |
|||
(77 intermediate revisions by 33 users not shown) | |||
Line 1: | Line 1: | ||
{{talk header}} |
|||
{{WPDATABASE|class=GA|importance=mid}} |
|||
{{ArticleHistory |
{{ArticleHistory |
||
|action1=GAN |
|action1=GAN |
||
|action1date=2007-05-11, 15:04:16 |
|action1date=2007-05-11, 15:04:16 |
||
|action1link=Talk:Null (SQL)/Archive 1#GA Review |
|||
|action1result=listed |
|action1result=listed |
||
|action1oldid=130098577 |
|action1oldid=130098577 |
||
Line 8: | Line 9: | ||
|action2=PR |
|action2=PR |
||
|action2date=2007-05-17, 09:36:06 |
|action2date=2007-05-17, 09:36:06 |
||
|action2link=Wikipedia:Peer review/Null (SQL) |
|action2link=Wikipedia:Peer review/Null (SQL)/archive1 |
||
|action2oldid=131479610 |
|action2oldid=131479610 |
||
Line 17: | Line 18: | ||
|action3oldid=132135229 |
|action3oldid=132135229 |
||
|action4=GAR |
|||
|currentstatus=GA |
|||
|action4date=21:40, 6 November 2008 (UTC) |
|||
|topic=Engtech}} |
|||
|action4result=kept |
|||
|action4oldid=250106866 |
|||
|topic=Engtech |
|||
|action5 = GAR |
|||
|action5date = 13:07, 8 January 2024 (UTC) |
|||
|action5link = Wikipedia:Good article reassessment/Null (SQL)/1 |
|||
|action5result = delisted |
|||
|action5oldid = 1192686461 |
|||
|currentstatus = DGA |
|||
}} |
|||
{{WPBS|class=C |1= |
|||
{{WikiProject Computing|importance=mid}} |
|||
{{WPDATABASE|importance=mid}} |
|||
}} |
|||
{{todo}} |
{{todo}} |
||
{{User:MiszaBot/config | algo = old(365d) | archive = Talk:Null (SQL)/Archive %(counter)d | counter = 1 | maxarchivesize = 150K | archiveheader = {{Automatic archive navigator}} | minthreadstoarchive = 1 | minthreadsleft = 10 }} |
|||
== I think this is duplicate page == |
|||
==Re-wrote== |
|||
I fleshed out this article for you a bit. Enjoy.[[User:SqlPac|SqlPac]] 03:09, 23 March 2007 (UTC) |
|||
:Nice Additions/Changes Stolze. One thing that might be worth mentioning (maybe?) is that NULL has been around since the original SQL standard? It might also be good to gather some information about how NULL is handled on different database systems. I understand there are some that implement Dr. Codd's A-Value/I-Value system, and others that do some strange things with NULL under some circumstances.[[User:SqlPac|SqlPac]] 15:44, 28 March 2007 (UTC) |
|||
::Since we can't collect a complete list of different implementations, I suggest that we don't add this here beyond what we already have. --[[User:Stolze|Stolze]] 19:20, 8 May 2007 (UTC) |
|||
:::Yeah, I definitely can't get a complete list together, or documentation on their inconsistencies. So I can agree with leaving it as is for now. I'm thinking an "SQL" project might be in order here :) Do you know how to propose a project like that? [[User:SqlPac|SqlPac]] 16:09, 9 May 2007 (UTC) |
|||
==Added Info== |
|||
Added more to the Comparisons and Three-Valued Logic section, under 4 sub-sections. If someone can provide more specifics about NULL comparisons and SQL procedural extensions on other platforms (DB2, Oracle, MySQL, etc.), it would be appreciated. Also added more information in Grouping and Sorting NULL section, Aggregate Functions and NULL (added example of NULL-elimination causing differences in results). Enjoy.[[User:SqlPac|SqlPac]] 18:23, 2 April 2007 (UTC) |
|||
:I wouldn't add DBMS-specific things here. Specific comparison articles (there was one for different SQL constructs) are a more proper way to provide such information. --[[User:Stolze|Stolze]] 15:00, 3 April 2007 (UTC) |
|||
::I'm not talking about a full-blown article on all the differences between platforms, but rather pointing out a few specific examples of inconsistencies (I'm sure there are some) in the way different platforms handle NULLs.[[User:SqlPac|SqlPac]] 21:07, 9 April 2007 (UTC) |
|||
:::I can agree to that. :-) --[[User:Stolze|Stolze]] 21:31, 14 April 2007 (UTC) |
|||
::::I can research some, but I'm not familiar enough with other platforms to be able to come up with much or verify myself. I am aware that FirstSQL implements Codd's "A-Value" and "I-Value" NULL replacements, and I understand that Oracle has some inconsistencies in the way it handles NULL in user-defined types and I believe I've read somewhere that it can store (or retrieve?) empty strings instead of actual NULL in a column even though NULL is specified in some situations. I'll see if I can find more info on these items, but anyone with knowledge of NULL-handling peculiarities on platforms other than SQL Server feel free to jump in. Thanks :)[[User:SqlPac|SqlPac]] 02:37, 16 April 2007 (UTC) |
|||
==NULL, Empty Strings and Zero== |
|||
Added a section with a couple of paragraphs on NULL vs. Empty Strings and Zero. ALso added references and a couple of other minor edits. I hope I got the reference formats correct, although the information is accurate the layout probably needs to be tweaked a bit.[[User:SqlPac|SqlPac]] 02:43, 16 April 2007 (UTC) |
|||
==Peer Review Feedback (Automated)== |
|||
We have some 'automated' feedback from the peer review request. Here are all of the recommendations from [http://en.wikipedia.org/wiki/Wikipedia:Peer_review/Automated/May_2007#Null_.28SQL.29 Automated Peer Review Results] made by [[User:Ruhrfisch|Ruhrfisch]]: |
|||
'''[[Null (SQL)]]''' |
|||
'''============''' |
|||
The following suggestions were generated by a semi-automatic [[User:AndyZ/peerreviewer|javascript program]], and might not be applicable for the article in question. |
|||
*<s>Consider adding more [[WP:LINK|links]] to the article; per [[Wikipedia:Manual of Style (links)]] and [[Wikipedia:Build the web]], create links to relevant articles.</s><sup>[[User:AndyZ/G#underlink|[?]]]</sup> |
|||
**Added a lot more links. [[User:SqlPac|SqlPac]] 03:44, 6 May 2007 (UTC) |
|||
*<s>This article has no [[WP:IMAGE|images]]. Please see if there are any [[WP:IT|free use]] images that fall under the [[Wikipedia:Image use policy]] and fit under one of the [[Wikipedia:Image copyright tags]] that can be uploaded. To upload images on Wikipedia, go to [[Special:Upload]]; to upload non-[[WP:FU|fair use]] images on the [[Wikimedia Commons]], go to [[commons:special:upload]].<sup>[[User:AndyZ/G#noimg|[?]]]</sup></s> |
|||
**<s>I don't know of any applicable images? Any suggestions out there folks? Thanks. [[User:SqlPac|SqlPac]] 03:44, 6 May 2007 (UTC)</s> |
|||
*<s>See if possible if there is a [[WP:IT|free use]] image that can go on the top right corner of this article.<sup>[[User:AndyZ/G#leadimg|[?]]]</sup></s> |
|||
**<s>I don't know of any applicable images here either? Any suggestions guys 'n gals? Thanks. [[User:SqlPac|SqlPac]] 03:44, 6 May 2007 (UTC)</s> |
|||
::: I have no idea how to visualize NULLs. It is an abstract concept. So I wouldn't add images either. (That would be bound to lots of discussion anyway. --[[User:Stolze|Stolze]] 19:19, 8 May 2007 (UTC) |
|||
:::: I was thinking that we might get a "database" image - one of those "SQL Cylinder" images that's so popular as an icon for this article (and maybe for other SQL-related articles). I'm no graphic artist though :) The only thing that makes sense for a NULL image in the article (to me) would be a screenshot of a query result that has NULLs in it? I don't know how much value that would really add to the article though. [[User:SqlPac|SqlPac]] 16:06, 9 May 2007 (UTC) |
|||
::::: I added a Null "icon" made from some images I located at Wikimedia commons. The images were placed there by their creator under the LGPL license. Let me know what you think. (Please be kind - I'm no graphic artist). Thanks. [[User:SqlPac|SqlPac]] 00:26, 17 May 2007 (UTC) |
|||
::::: This may sound lame, but I think it could look good: a simple screenshot of a query and its result -- for example a PostgreSQL query with some null results in an xterm. I used something similar for a presentation about a buzillion years ago and it worked nicely. Cheers. <span style="font-family:Bradley Hand ITC;">[[User:Haus|<font style="color:Blue;"><font size="+2">H</font>aus</font>]]<sup style="font-size : 6pt ;">[[User_talk:Haus|<font style="color:Green;">Talk</font>]]</sup></span> 00:46, 12 May 2007 (UTC) |
|||
:::::: Added a screenshot of the result set of an outer join that produces Nulls. I included both the SQL query and the result set in the screenshot. I think it actually doesn't look too bad. Let me know what you guys think. Thanks. [[User:SqlPac|SqlPac]] 01:15, 17 May 2007 (UTC) |
|||
:::::::I wouldn't use a screenshot because each database system has its own way to display NULLs, and each system suffers from the problem that you often can't distinguish it from a string. This is particularly true in the example that is in the article right now. Alternatively, at least explain in the description of the picture the source of the picture, DBMS used, and that the NULL (with the differently-colored background) is not a string but an indicator for NULL. --[[User:Stolze|Stolze]] 09:34, 19 May 2007 (UTC) |
|||
*<s>There may be an applicable [[WP:INFOBOX|infobox]] for this article. For example, see [[Template:Infobox Biography]], [[Template:Infobox School]], or [[Template:Infobox City]].<sup>[[User:AndyZ/G#infobox|[?]]]</sup> (Note that there might not be an applicable infobox; remember that these suggestions are not generated manually)</s> |
|||
**<s>I couldn't find an applicable [[WP:INFOBOX|infobox]], but that doesn't mean there's not one. Anyone out there know if there is one that would fit? Thanks.</s> [[User:SqlPac|SqlPac]] 03:44, 6 May 2007 (UTC) |
|||
***There's no applicable [[WP:INFOBOX|infobox]] for this article currently. [[User:SqlPac|SqlPac]] 15:17, 11 May 2007 (UTC) |
|||
*<s>Per [[Wikipedia:Only make links that are relevant to the context|Wikipedia:Context]] and [[Wikipedia:Build the web]], years with full dates should be linked; for example, link [[January 15]], [[2006]].</s><sup>[[User:AndyZ/G#linkdate|[?]]]</sup> |
|||
**Added links to dates in article. [[User:SqlPac|SqlPac]] 03:44, 6 May 2007 (UTC) |
|||
*<s>Per [[Wikipedia:Manual of Style (headings)]], headings generally should not repeat the title of the article. For example, if the article was [[Ferdinand Magellan]], instead of using the heading ''<nowiki>==Magellan's journey==</nowiki>'', use ''<nowiki>==Journey==</nowiki>''.</s><sup>[[User:AndyZ/G#headingre|[?]]]</sup> |
|||
**Changed article headings to match [[Wikipedia:Manual of Style (headings)]] requirements. [[User:SqlPac|SqlPac]] 03:44, 6 May 2007 (UTC) |
|||
*<s>Please reorder/rename the last few sections to follow guidelines at [[Wikipedia:Guide to layout]].</s><sup>[[User:AndyZ/G#gtl|[?]]]</sup> |
|||
**Re-ordered final sections to match [[Wikipedia:Guide to layout]]. [[User:SqlPac|SqlPac]] 03:44, 6 May 2007 (UTC) |
|||
*Watch for [[User:Tony1/How_to_satisfy_Criterion_2a#Redundancy|redundancies]] that make the article too wordy instead of being crisp and concise. (You may wish to try Tony1's [[User:Tony1/How to satisfy Criterion 1a: redundancy exercises|redundancy exercises]].) |
|||
**<s>Vague terms of size often are unnecessary and redundant - “some”, “a variety/number/majority of”, “several”, “a few”, “many”, “any”, and “all”. For example, “<font color='red'><s>All</s></font> pigs are pink, so we thought of <font color='red'><s>a number of</s></font> ways to turn them green.”</s> |
|||
***<s>Did a quick run-through, but didn't do a thorough check for this one yet.</s> [[User:SqlPac|SqlPac]] 03:44, 6 May 2007 (UTC) |
|||
***Removed several occurrences of "some" and "many". In some places, though, "some" and "many" seem more appropriate and concise than the alternative. [[User:SqlPac|SqlPac]] 15:28, 11 May 2007 (UTC) |
|||
*<s>Avoid using contractions like (outside of quotations): ''isn't''.</s> |
|||
**<s>Didn't do a thorough check yet.</s> [[User:SqlPac|SqlPac]] 03:44, 6 May 2007 (UTC) |
|||
**Fixed by <span style="font-family:Bradley Hand ITC;">[[User:Haus|<font style="color:Blue;"><font size="+2">H</font>aus</font>]]<sup style="font-size : 6pt ;">[[User_talk:Haus|<font style="color:Green;">Talk</font>]]</sup></span> [[User:SqlPac|SqlPac]] 15:15, 11 May 2007 (UTC) |
|||
*Please ensure that the article has gone through a thorough copyediting so that it exemplifies some of [[WP:WIAFA|Wikipedia's best work]]. See also [[User:Tony1/How to satisfy Criterion 1a]].<sup>[[User:AndyZ/G#copyedit|[?]]]</sup> |
|||
**We'll see what we can do. [[User:SqlPac|SqlPac]] 03:44, 6 May 2007 (UTC) |
|||
You may wish to browse through [[User:AndyZ/Suggestions]] for further ideas. Thanks, [[User:Ruhrfisch|Ruhrfisch]] 03:17, 5 May 2007 (UTC) |
|||
== Missing reference == |
|||
I can't access the 3rd manifesto right now for some reason. But I assume it states that it is an inherent property of values to be comparable to other values of the same data type and that NULL does not satisfy this criteria - hence, NULL is not a value. Hugh Darwen told me this argument a few years back, so I would think we should find it there. --[[User:Stolze|Stolze]] 19:15, 8 May 2007 (UTC) |
|||
:Cool, it sounds like something Darwen or Date would say. I'd just like to get a good reference to it in there, in case someone questions it. [[User:SqlPac|SqlPac]] 16:04, 9 May 2007 (UTC) |
|||
== Common Mistakes, Empty Strings, and Zero == |
|||
The new Common Mistakes section seems to be expanding on the existing discussion in the Empty Strings and Zero section. I recommend combining the Empty Strings and Zero section with the Common Mistakes section. I'd do it myself (and I may later), but don't have time at the moment. [[User:SqlPac|SqlPac]] 21:10, 10 May 2007 (UTC) |
|||
*Sounds good to me. – [[User:Quadell|Quadell]] <sup>([[User_talk:Quadell|talk]]) ([[Special:Random|random]])</sup> 00:52, 11 May 2007 (UTC) |
|||
**Done. Might want to take a look to make sure it still flows properly. Thanks.[[User:SqlPac|SqlPac]] 02:41, 12 May 2007 (UTC) |
|||
== 2 quick comments == |
|||
I reordered the contents of the 1st sentence a little, feeling the change improves the flow a little. If you disagree, please don't hesitate to revert the edit. |
|||
Unless there's a really good counterargument that I'm not aware of, it seems that every occurrence of the word should be html-formatted as code (i.e.: <code>NULL</code>). Cheers. <span style="font-family:Bradley Hand ITC;">[[User:Haus|<font style="color:Blue;"><font size="+2">H</font>aus</font>]]<sup style="font-size : 6pt ;">[[User_talk:Haus|<font style="color:Green;">Talk</font>]]</sup></span> 12:45, 11 May 2007 (UTC) |
|||
:Generally (in print anyway) the word NULL is formatted as code when it's referring to the actual keyword. That differentiates the physical implementation (the keyword <code>NULL</code>) from the concept of NULL. I wouldn't be averse to changing the non-keyword references to NULL to mixed-case (Null) to differentiate them, but I don't think they should all be formatted as <code>NULL</code>. Thanks [[User:SqlPac|SqlPac]] 13:47, 11 May 2007 (UTC) |
|||
:: Ahh, I see. This duality suggests a tweak to the first paragraph. Reading back over it, I see that the distinction is made, but it's made pretty subtly. Something along the lines of "The word '''NULL''' has two uses in the Structured Query Language (SQL): ..." might underscore the two uses. Cheers. <span style="font-family:Bradley Hand ITC;">[[User:Haus|<font style="color:Blue;"><font size="+2">H</font>aus</font>]]<sup style="font-size : 6pt ;">[[User_talk:Haus|<font style="color:Green;">Talk</font>]]</sup></span> 14:28, 11 May 2007 (UTC) |
|||
:::I went through and changed the references to Null concept to mixed case, normal font, the references to the keyword are in code font. Might still require some tweaking, but this should make it more clear. Thanks. [[User:SqlPac|SqlPac]] 14:36, 11 May 2007 (UTC) |
|||
==GA Review== |
|||
This article meets all the criteria listed on [[WP:WIAGA]] and [[Wikipedia:Reviewing_good_articles]] and recent edits have fixed the key issues in the above automated peer review. I'm happy to list it as a Good Article. Congrats on the good work. <span style="font-family:Bradley Hand ITC;">[[User:Haus|<font style="color:Blue;"><font size="+2">H</font>aus</font>]]<sup style="font-size : 6pt ;">[[User_talk:Haus|<font style="color:Green;">Talk</font>]]</sup></span> 15:04, 11 May 2007 (UTC) |
|||
==FA Review== |
|||
Some comments from the [http://en.wikipedia.org/wiki/Wikipedia:Featured_article_candidates#Null_.28SQL.29 FAC Null(SQL) FA Review]: |
|||
*The article reads too much like a "programming guide", and is not "dumbed down" enough |
|||
*The article should include a larger discussion of the history of Null |
|||
*The article should include more on Null and Relational Theory (set-based mathematics) |
|||
*The article should be edited to make it flow better, easier to read |
|||
I agree with most of the recommendations, although I don't believe our goal should be to "dumb down" the article. Maybe the reviewer meant to "make it more accessible"? At any rate, adding the mathematics of Null into the discussion is definitely not going to facilitate a "dumbing down". I've asked for more specific guidance. In the meantime, maybe we should look at removing some of the specific programming examples to make it more accessible to the layman? Comments? Thanks. [[User:SqlPac|SqlPac]] 18:26, 17 May 2007 (UTC) |
|||
:Edited down to the "Joins" heading. Have recombined a lot of pieces, added a history section at the top, shortened the intro, and removed a lot of code samples. Will do more later. [[User:SqlPac|SqlPac]] 19:37, 17 May 2007 (UTC) |
|||
::I don't think removing the examples of common mistakes was a good idea. After all, most people's introduction to how NULL works comes about because of just this misunderstanding. I don't think the article will be complete without this information. – [[User:Quadell|Quadell]] <sup>([[User_talk:Quadell|talk]]) ([[Special:Random|random]])</sup> 20:06, 17 May 2007 (UTC) |
|||
:::OK, we can probably put that back in. I just wanted to start bare bones without many code samples at all, since that's what the [[WP:FA]] reviewers seemed really upset by. I think we also need to make sure we sort of "lead in" and "hand hold" around the code samples too, just to make sure "laymen" can understand. Of course I think a lot of that understanding is going to be shot if we add in some of the complexities of the mathematics anyway :) Thanks [[User:SqlPac|SqlPac]] 20:13, 17 May 2007 (UTC) |
|||
::Edited all the way down to "Criticisms". I'll check back later. I'd like to expand the history section and talk about some of the following: |
|||
::*Old-style methods for representing missing data (i.e., "magic numbers") |
|||
::*Some of the math behind Nulls and the relational model (just the parts pertaining to Null) |
|||
::*The Open-World Assumption versus the Closed-World Assumption, and how Null fits (or doesn't fit) in with it |
|||
::I have a pretty good idea where to head with some of this, I just don't have the references handy. Anyone? Thanks :) [[User:SqlPac|SqlPac]] 20:13, 17 May 2007 (UTC) |
|||
:::For now I'm reverting back to the old version. I think I made some good textual edits, and will put them back in later. After the discussion over at the Computer Science WikiProject, I'm more of the mind that basically technical articles just have to be technical... and we can't really "dumb it down" and still make it effective or useful. I think we can make it more accessible without making it "dumber" :) [[User:SqlPac|SqlPac]] 02:52, 19 May 2007 (UTC) |
|||
Why has this page been written? In SQL, null has exactly the same meaning / behavior it has in ALL programming languages. The only thing that COULD (but not in my opinion) justify this page, would have been some discussion about UNKNOWN pseudo-value. But there is nothing like that, so this page is misleading for someone who doesn't know that null is. <span style="font-size: smaller;" class="autosigned">— Preceding [[Wikipedia:Signatures|unsigned]] comment added by [[Special:Contributions/151.21.9.214|151.21.9.214]] ([[User talk:151.21.9.214|talk]]) 14:37, 5 October 2011 (UTC)</span><!-- Template:Unsigned IP --> <!--Autosigned by SineBot--> |
|||
::::Good decision. I'm all in favor of having a good technical article and not a "Brangelina" on NULLs. ;-) So we should go even deeper into the technical details if necessary. (I believe that this article is already in a very good shape compared with a lot of other SQL-related articles.) If you would have "dumbed it down", I would probably have stopped working on it. |
|||
::::I agree on the three points you listed above, although I don't know exactly what you mean with "open-world" vs. "closed-world". Also, we probably don't have to expand too much on the relational theory, given that there are separate articles on those topics available already. --[[User:Stolze|Stolze]] 09:24, 19 May 2007 (UTC) |
|||
:::::I'd like to get the content up to [[WP:FA]] status, but no point selling our souls to do it :) If the best we can ever achieve is [[WP:GA]] status, then so be it. Date and Darwen have a big issue with Null because it operates under the "Open World Assumption", whereas SQL is supposed to be based (according to them) on the "Closed World Assumption". In the "Closed World Assumption" there are only True and False states -- nothing is considered Unknown. If it's not True, it's False; if you're not with us, you're against us; etc. I'd like to add a bit about how Null doesn't fit into the Closed World Assumption model of SQL. I'm not really looking to add a whole new article on relational theory or CWA, but rather just an explanation of how Null relates to them. [[User:SqlPac|SqlPac]] 21:22, 19 May 2007 (UTC) |
|||
== Amazing and amusing contradictions == |
|||
::::::That rings a bell now. Thanks for the explanation. Also, I'm glad that you have the same position on the status of SQL-related articles (or technical articles in general) as I do. Well, then we can go on and work on the content instead of discussions about it only. ;-) --[[User:Stolze|Stolze]] 13:29, 20 May 2007 (UTC) |
|||
:::::::Cool. Someone suggested an alternative which we might look at as well, which is to create an "A-class" rating policy for database-related articles on the [[WP:Database|Databases WikiProject]], and try to get articles up to that standard. As I understand it, "A-class" ratings create a project-specific parallel to the standards used by "FA-class" articles. I really don't like being told that a technical article can never be considered some of the "best" content on Wikipedia just because it's not dumbed-down enough for some reviewers. [[User:SqlPac|SqlPac]] 19:27, 20 May 2007 (UTC) |
|||
CJ Date says the SQL 3VL logic is none of those usually studied. John Grant says it's Kleene's K3. Thalheim and Schewe say it's Łukasiewicz L3. They can't all be right. What's more amusing is that they disagree about such basic facts. [[User:Tijfo098|Tijfo098]] ([[User talk:Tijfo098|talk]]) 19:44, 7 November 2012 (UTC) |
|||
== POLICY DEBATE: Use of source code and other examples in articles == |
|||
: Actually, this mystery has been solved, cf [http://www.is.informatik.uni-kiel.de/~hjk/sqlni.ps] p. 9. Basically it's both K3 and L3 because they differ only in their table for implication, but SQL has no such operand. And in this sense, SQL's is also an incompletely specified logic. So all of them (Date, Grant and Thalheim & Schewe) are correct! [[User:Tijfo098|Tijfo098]] ([[User talk:Tijfo098|talk]]) 16:25, 8 November 2012 (UTC) |
|||
Someone started a policy debate concerning the use of source code in articles, that seems to directly impact this (and possibly other Database articles). See below for details: |
|||
== Claimed inconsistency of boolean variables == |
|||
I have opened [[Wikipedia_talk:Attribution#Explicit_mention_of_examples_.28source_code.2C_math.2C_etc..29|a debate on the use of source code and other examples]] in Wikipedia articles. It seems that many pieces of example source code etc. currently in Wikipedia violate Wikipedia policy, so we need to either clarify or change the situation. Depending on the result of the discussion, this may result in a number of source code examples being summarily ''removed'' from computing articles! |
|||
The catch is that in implementations which distinguish boolean NULL from Unknown, like in that of Microsoft, an expression like TRUE OR NULL (e.g. "(1 <> 2) OR NULL") fails the type check, so you can't speak of its result, because it's a parsing error. Unless someone can produce a reliable source for this so-called inconsistency, I'll delete the claim. [[User:Tijfo098|Tijfo098]] ([[User talk:Tijfo098|talk]]) 15:31, 8 November 2012 (UTC) |
|||
Please reply there, not here, if you wish to contribute.—[[User:Greenrd|greenrd]] 10:50, 18 May 2007 (UTC) |
|||
* I've deleted it. [[User:Tijfo098|Tijfo098]] ([[User talk:Tijfo098|talk]]) 16:17, 10 November 2012 (UTC) |
|||
== Nulls are Equal ? == |
|||
Thanks.[[User:SqlPac|SqlPac]] 15:05, 18 May 2007 (UTC) |
|||
The chapter "When two nulls are equal: ..." seems to be outdated. Actually it reads: "Because SQL:2003 defines all Null markers as being unequal to one another...". The content of the chapter is not incorrect, but the actual standard uses more elegant expressions - with a slightly different semantic. SQL:2011 defines in ''4.1.5 Properties of distinct'' the rule "Two null values are not distinct.". Is this only a problem of linguistic representation within the wiki-article or is it a progress in the SQL standard, which is not reflected by the article? As far as I know, the semantic of GROUP BY and other related language elements hasn't changed from 2003 to 2011. --[[User:Kelti|Kelti]] ([[User talk:Kelti|talk]]) 19:57, 11 May 2014 (UTC) |
|||
:I added my comments in the other discussion. --[[User:Stolze|Stolze]] 09:14, 19 May 2007 (UTC) |
|||
== Where are nulls explicitly used? == |
|||
==Wiki Giveth, Wiki Taketh Away?== |
|||
It appears that the article has now been submitted for [[WP:FAR|Featured Article Review]], where debate is held concerning whether an article should be removed from [[WP:FA|Featured Article]] status. It appears that no one (as of yet) has voiced any concerns about the content or quality of the article, nor have they voiced any support for the objections raised by the two featured article reviewers who responded to the nomination. I've asked what can be done to get the article beyond the review process, but have been told that the article itself is "not in question" at this time. It appears that someone is questioning the article's [[WP:FA]] status based on a technicality of some sort, and are unwilling to share what they think is wrong with the article with the contributors at this time. This is way too funny. [[User:SqlPac|SqlPac]] 03:55, 28 May 2007 (UTC) |
|||
I think this article needs to illustrate a real-world example where nulls are explicitly used, so we can see their use (if a use exists). I used SQL a little in work for 8 months, never needed to assign a NULL value to any field (but it was only 8 months of light SQL work after all). |
|||
::Oops, FAC failed. My motivation to do anything on here has taken a serious hit over the past week. Oh well. [[User:SqlPac|SqlPac]] 04:14, 30 May 2007 (UTC) |
|||
If there are legit uses of explicit nulls, then we should probably NOT get rid of them. If we did, then anyone who needed them would just add an extra boolean field called NULL after every normal field, and thus use them again. The difference would be, they would have to implement the logic themselves and thus reinvent the wheel. If I'm not mistaken, I believe there are flags in a table that you can set to ensure that no field can ever be NULL, and if you really fear/hate NULLS you can do things safely this way. |
|||
::: Sorry for the headache, but you can resubmit this to FAC at any time. [[User_talk:Gimmetrow|''Gimmetrow'']] 08:23, 30 May 2007 (UTC) |
|||
Also, the "Closed World" argument was good, but in the real world, sometimes certain things are unknown. Again, a real-world example is needed and unfortunately I can't think of one myself due to my limited SQL experience. [[User:DrZygote214|DrZygote214]] ([[User talk:DrZygote214|talk]]) 00:33, 18 April 2015 (UTC) |
|||
:::: No thank you, not interested at all. [[User:SqlPac|SqlPac]] 19:29, 6 June 2007 (UTC) |
|||
:Regarding "''I believe there are flags in a table that you can set to ensure that no field can ever be NULL''", see the section ''Check constraints and foreign keys'' in the article, starting at "In order to constrain a column to reject Nulls..." For example, to specify that a user registered with a website must have a handle, the following can be used in the user table definition: |
|||
===Suggested improvements=== |
|||
::<code>username varchar(255) not null unique</code> |
|||
:They are also important in the event that a record has missing fields. Instead of recording default values for such fields, the field is marked as "null", which basically signifies it is empty. (See also [http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm Nulls] in the Oracle Database SQL Reference.) AS noted in the criticism section of this article. a null value signifies that there is no value assigned for that field, not that the field has a value of 0 (zero) or some such. [[User:Mindmatrix|<span style="color:#8b4513;">Mind</span>]][[User_talk:Mindmatrix|<span style="color:#ee8811;">matrix</span>]] 00:58, 18 April 2015 (UTC) |
|||
::: In real life "I don't know yet" is often the answer to a question. Even a Yes/No question has 3 possible answers (not 2): "Yes" (I know the fact and it is yes), "No" (I know the fact and it is no), and "I don't know yet" (I don't know the fact yet because it is [[to be determined]]). And with numbers, the same is true. For example, for each day, enter the weather data, such what the high temperature was. Let's say that in 2013, there were 2 days, July 23 and July 24, when no thermometer reading was recorded. Should you just say that the high temperature that day was [[zero]] degrees? Damn, that's funny, the [[average]] temperature in July 2013 was strangely cool! No, it wasn't—you just didn't know how to design your database. <br/>   This is a basic aspect of [[information science]], and [[information technology]] should be designed to handle it, which is why [[database design]] and [[database theory]] have the null concept. Another way to say it is that a value of [[zero]] or [[no]] is not the same thing as the lack of a value. In other words, [[zero]] and [[no]] are information, whereas null is the lack of information, or, more precisely, a piece of [[meta-information]] that informs about a lack of information (or [[metadata]] about a lack of data). Going months or years without having to consider that distinction is a reflection of the application—that is, of the particular project being worked on: it was evidently a context in which every relevant piece of information was already known at the time of data entry. But in other applications (other projects), one may not be so lucky. It is quite true that a field property can be set to "required" (which means "no nulls"). But whether any particular database is reasonably able to use (or ought not to use) that property on any particular field is determined by the subject matter at hand. See the "July 23 and July 24" example. It should not be about each person's "fear of nulls" or reinvention of the null concept. The concept is already well known. <br/>   Related: The critique of the null concept did not really belong in this article's lede. It belongs in the article body. The only people who can properly understand its importance or context are people who have high [[database theory]] and [[database design]] knowledge. [[Computer science]] PhD types. Not me, not the average reader of Wikipedia. As evidenced by the question posed in this talk thread, that is not the average user of this Wikipedia article, and the discussion as currently placed and written in the lede was probably confusing more people than it is helping. I moved it down. First tell a database 101 student what null is and what it's good for. Discuss the advanced philosophical critique later, in a section that 101 students will skip over. — [[User:Three-quarter-ten|¾-10]] 17:35, 18 April 2015 (UTC) |
|||
While we wait for Raul to comment on the FAR there are in fact things that need to be improved. Most obviously the lead, which is too short and has a one sentence paragraph tacked-on. I know nothing about the topic, but a good rule-of-thumb is mentioning what the TOC mentions. Could sentences be added regarding three valued logic and a short description of criticisms? I'm not sure about italicizing values (''unknown'' etc.)—is that what's usually done on articles of this sort? Also, there are unformatted weblinks in the references and no access dates. This should be taken care of. [[User:Marskell|Marskell]] 09:31, 29 May 2007 (UTC) |
|||
:::: I was going to add, but forgot, the acknowledgment that [[RDBMS]]s do have binary field data type (for 0/1, Yes/No, On/Off, True/False), which cannot give 3-value logic including null, but along with that fact comes the corollary that during database design, if that data type is chosen at all, it really should be chosen only for data where it is highly likely (and seems likely to remain likely) that at the time of data entry the person entering the data will always already know what the fact is. Otherwise the designer should use an integer data type that allows 3 options. An example of a website that explores this point is [http://allenbrowne.com/noyesno.html http://allenbrowne.com/noyesno.html]. — [[User:Three-quarter-ten|¾-10]] 23:29, 19 April 2015 (UTC) |
|||
:Thanks, I'll get right on it. The italicizing is often done in print materials (computer books, etc.), but if it's not Wikipedia style, I'll change it. The intro. was actually cut down based on previous reviewer comments, but we can expand it again, no problem. [[User:SqlPac|SqlPac]] 19:21, 29 May 2007 (UTC) |
|||
== The part == |
|||
::Made recommended changes. Thanks! [[User:SqlPac|SqlPac]] 20:40, 29 May 2007 (UTC) |
|||
The section “Analysis of SQL Null missing-value semantics” |
|||
===Mistake=== |
|||
seems to provide '''private Nonsens''': |
|||
The “groundbreaking work of T. Imielinski and W. Lipski (1984)” |
|||
Raul has [[User_talk:Raul654#Null_.28SQL.29_at_FAR|indicated]] that this was in fact mistakenly promoted. SqlPac, I can understand that this is very annoying, but we're all people here. I would suggest taking this back to FAC and considering it a restarted nom. [[User:Marskell|Marskell]] 07:37, 30 May 2007 (UTC) |
|||
'''was and is completely unknown''' to the database community. |
|||
:GimmeBot and Gimmetrow will straighten out the articlehistory on the talk page. [[User:SandyGeorgia|Sandy<font color="green">Georgia</font>]] ([[User talk:SandyGeorgia|Talk]]) 08:09, 30 May 2007 (UTC) |
|||
:: Per Raul654, I've treated this is a failed nomination. I'm not adding [[Wikipedia:Featured article review/Null (SQL)]] to ArticleHistory since there isn't much of a review there, and this note on the talk page should be enough. The article can be resubmitted to FAC any time. [[User_talk:Gimmetrow|''Gimmetrow'']] 08:23, 30 May 2007 (UTC) |
|||
:::Thanks [[User:Marskell|Marskell]], but I'm not really interested in going through any of this "process" again. Let someone else deal with these big [[WP:FA|FAckers]]. [[User:SqlPac|SqlPac]] 23:46, 30 May 2007 (UTC) |
|||
The proposition |
|||
==Explanation of removal of ''programming language''== |
|||
“and if is its lifting to a construct intended to represent missing information” |
|||
I just removed the clause identifying SQL as a programming language, which it is not. The first use of ''Structured Query Language (SQL)'' is wikilinked, and that page is where it should be explained that there are proprietary extensions to SQL (e.g. Transact-SQL, PL/SQL) that add programming capabilities. |
|||
has '''no truth value''', thus no content. <small class="autosigned">— Preceding [[Wikipedia:Signatures|unsigned]] comment added by [[Special:Contributions/94.219.112.101|94.219.112.101]] ([[User talk:94.219.112.101|talk]]) 17:47, 13 May 2015 (UTC)</small><!-- Template:Unsigned IP --> <!--Autosigned by SineBot--> |
|||
== What is the binary representation of Null? == |
|||
:SQL/PSM is ISO standard SQL. It adds "programming capabilities" to SQL without proprietary extensions. It reads about the same to me either way though, so I could leave your changes in place AFAIC. Thanks. [[User:SqlPac|SqlPac]] 01:27, 8 June 2007 (UTC) |
|||
What exactly does a SQL engine put "on disk" when it writes Null into a column? Example, if I have a column of type INTEGER, all values between binary "00000000 00000000 00000000 00000000" and "11111111 11111111 11111111 1111111" can be stored, each representing an integer number. If this column also allows "Null" as a value, what exactly is written onto the disk? There's nothing left that can mean "Null", because any bit sequence already stands for some INTEGER value. --[[Special:Contributions/194.231.113.66|194.231.113.66]] ([[User talk:194.231.113.66|talk]]) 13:29, 17 July 2015 (UTC) |
|||
::Thanks, SqlPaq. I just revised the scope section of the [[SQL]] article to include [[SQL/PSM]] and created a stub article for PSM. I'd still argue that PSM is an extension to SQL, not part of SQL. Otherwise we get that nasty 'language-language' name. |
|||
:The field in the database is not assigned a value in the case of NULL; it is empty. (This is the typical case, though some DB may be implemented differently.) See also the discussion [[#Where are nulls explicitly used?|Where are nulls explicitly used?]] above. [[User:Mindmatrix|<span style="color:#8b4513;">Mind</span>]][[User_talk:Mindmatrix|<span style="color:#ee8811;">matrix</span>]] |
|||
:::No problem. You're right, [[SQL/PSM]] is defined by the standard as an extension to SQL, but it's supposed to replace ''proprietary'' extensions like PL/SQL and T-SQL. Of course it's about 20 or so years overdue :) The standard also defines several other extensions, including SQL/MM (multimedia), SQL/XML, SQL/MED (Management of External Data), SQL/CLI (Call Level Interface), and others. There are also others under consideration, like temporal data extensions, etc. God knows if they'll ever get those right :) I'll get a list of currently defined extensions together if there's not one in the [[SQL]] article already. Thanks. [[User:SqlPac|SqlPac]] 01:26, 10 June 2007 (UTC) |
|||
:I would hazard that this is implementation specific. Even a given SQL implementation may have multiple engines (e.g. Maria has, for example [[innodb]]) which may be implemented in multiple architectures. |
|||
==Conversion to other languages== |
|||
:All the best: ''[[User:Rich Farmbrough|Rich]] [[User talk:Rich Farmbrough|Farmbrough]]'',<small> 21:36, 21 November 2016 (UTC).</small><br /> |
|||
There were three language tags on the page: |
|||
==NULL, Null, or null== |
|||
<nowiki>[[</nowiki>de:Nullwert]] |
|||
I would suggest that "null" is used for the general concept and "NULL" the SQL implementation. While a lot of the SQL I write uses Null or null, it seems that listing a table with a null field will show NULL. This makes it the "natural" representation in SQL. Unless anyone has a better idea? All the best: ''[[User:Rich Farmbrough|Rich]] [[User talk:Rich Farmbrough|Farmbrough]]'',<small> 21:39, 21 November 2016 (UTC).</small><br /> |
|||
<nowiki>[[</nowiki>pt:Null (SQL)]] |
|||
<nowiki>[[</nowiki>ru:NULL (SQL)]] |
|||
: The ISO SQL specification (ISO/IEC 9075-2:2016(E)) uses upper case for the <code>NULL</code> keyword, and lower case for the <I>null value</I>, but never Null. [[User:Fjerdingen|Fjerdingen]] ([[User talk:Fjerdingen|talk]]) 20:46, 8 June 2017 (UTC) |
|||
The [[de:Nullwert]] article is a completely different article that covers the concept of Null in computers in general, as opposed to Null in SQL specificially. The other two, [[pt:Null (SQL)]] and [[ru:NULL (SQL)]] are like really old, old translations of this article when it was a stub. If anyone can help us translate this article to other languages, we can use the help. Thanks! [[User:SqlPac|SqlPac]] 00:42, 22 June 2007 (UTC) |
|||
== NULL == |
|||
English: Please help us translate the updated article at [[en:Null (SQL)]]. |
|||
Several examples, some said to be ANSI/ISO SQL compliant, have syntax with <code>= NULL</code> - which is syntactically invalid. Those examples need to be corrected! [[User:Fjerdingen|Fjerdingen]] ([[User talk:Fjerdingen|talk]]) 20:21, 31 May 2017 (UTC) |
|||
Russian: Пожалуйста помогите нам перевести updated статью на [[en:Null (SQL)]]. |
|||
: In according to the SQL standard as well as to our [[b:Structured_Query_Language/NULLs_and_the_Three_Valued_Logic#Step_1:_Evaluation_of_NULLs|SQL wikibook]] the expression <code>col_x = NULL</code> is syntactically correct and leads to the boolean value <code>UNKNOWN</code>. As long as someone thinks in the usual two-value-logic of many programming languages, this behaviour may be confusing. In opposite to such languages SQL allows the NULL marker as a legal 'content' of columns and defines rules how to evaluate expressions with the literal NULL (or NULL markers in the column of a row) to true/false/unknown. If someone searches rows containing the NULL marker, the expression <code>col_x IS NULL</code> shall be used: it leads to <code>TRUE</code> for all rows containing the NULL marker. --[[User:Kelti|Kelti]] ([[User talk:Kelti|talk]]) 08:10, 2 June 2017 (UTC) |
|||
:: When it comes to confirming ISO/ANSI SQL syntax compliance I use the Mimer SQL-2003 Validator (http://developer.mimer.com/validator/parser200x/index.tml). If you run the query <code>SELECT * FROM T WHERE C = NULL</code> the result is "syntax error: NULL correction: <identifier>". However, this is the SQL-2003 standard, and there are later revisions of the ISO/ANSI SQL standard. Can you give me a reference that the syntax later has become valid? [[User:Fjerdingen|Fjerdingen]] ([[User talk:Fjerdingen|talk]]) 19:33, 5 June 2017 (UTC) |
|||
::: a) The syntax of the standard did not change over time. Since the very first days "= null" was syntactically correct - and often missleads people. But Mimer seems to change their behaviour. Their SQL-92 validator http://developer.mimer.se/validator/parser92/index.tml#parser gives a different answer to the example. I interpret the Mimer answer in their 200x parser as a hint because this query NEVER returns any row. |
|||
::: b) Please read the standard. SQL:2011, part-2, page 33 says "... any comparison involving the null value or an Unknown truth value will return an Unknown result.". [[User:Kelti|Kelti]] ([[User talk:Kelti|talk]]) 08:06, 6 June 2017 (UTC) |
|||
:::: It seems like we are discussing two different things here. I am talking about the <code>NULL</code> <b>keyword</b>, the syntactical element. Your SQL-2011 reference is about the <b><I>Null value</I></b>. Of course the <I>Null value</I> can be used when doing a comparison. However, this doesn't mean the <code>NULL</code> keyword is syntactically allowed. |
|||
:::: However, the syntax has been changed. As you pointed out, <code> = NULL</code> was syntactically correct in SQL-92. But between SQL-92 and SQL-99, the ISO SQL standardization committee worked hard on cleaning up old sins. One of those things were the <code> = NULL</code> syntax - which was removed! (Once in a while things get deprecated and removed from the SQL standard, e.g. <code>select null from table</code>, ordinal position in ORDER BY clause, joins without the <code>SELECT</code> keyword etc.)[[User:Fjerdingen|Fjerdingen]] ([[User talk:Fjerdingen|talk]]) 19:28, 7 June 2017 (UTC) |
|||
Portuguese: Ajude-nos por favor traduzir o artigo updated em [[en:Null (SQL)]]. |
|||
::::: Your distinction between the <code>NULL</code> keyword and a <code>null value</code> in a column (regarding to the SQL syntax) is new to me. SQL:2011, part-1, page 14 says: "... Every data type includes a special value, called the null value, sometimes denoted by the keyword NULL." Isn't this a statement that both are interchangeable? [[User:Kelti|Kelti]] ([[User talk:Kelti|talk]]) 07:42, 8 June 2017 (UTC) |
|||
Spanish: Ayúdenos por favor a traducir el artículo actualizado en [[en:Null (SQL)]]. |
|||
:::::: The <code>NULL</code> keyword is a <null specification> according to the ISO SQL specification (<code><null specification> ::= NULL</code>). (And the value of a <null specification> is the null value.) The <null specification> is not a part of the <row value predicand>, which is the argument to a comparison (<comparison predicate>). Below is some copy and paste from the SQL 2016 spec (ISO/IEC 9075-2:2016(E)), sorry for the ruined formatting: |
|||
German: Helfen Sie uns bitte, den aktualisierten Artikel neu zu übersetzen [[en:Null (SQL)]]. |
|||
8.2 |
|||
French: Svp aidez-nous à traduire l'article mis à jour à [[en:Null (SQL)]]. |
|||
<comparison predicate> ::= <row value predicand> <comparison predicate part 2> |
|||
<comparison predicate part 2> ::= <comp op> <row value predicand> |
|||
<comp op> ::= <equals operator> | <not equals operator> | <less than operator> | <greater than operator> | <less than or equals operator> | <greater than or equals operator> |
|||
7.2 |
|||
Please excuse the translations - courtesy of Babelfish. [[User:SqlPac|SqlPac]] 00:54, 22 June 2007 (UTC) |
|||
<row value predicand> ::= <row value special case> | <row value constructor predicand> |
|||
<row value special case> ::= <nonparenthesized value expression primary> |
|||
6.3 |
|||
:While I could probably translate the article to German, my take on such technical things is to stick with English. Especially manuals are sometimes translated in a rather strange way (to say it politely) and I usually recommend the English version. But maybe I will do this at some point in the future anyways. --[[User:Stolze|Stolze]] 00:05, 26 June 2007 (UTC) |
|||
<nonparenthesized value expression primary> ::= <unsigned value specification> | <column reference> | <set function specification> | <window function> | <nested window function> | <scalar subquery> | <case expression> | <cast specification> | <field reference> | <subtype treatment> | <method invocation> | <static method invocation> | <new specification> | <attribute or method reference> | <reference resolution> | <collection value constructor> | <array element reference> | <multiset element reference> | <next value expression> | <routine invocation> | <row pattern navigation operation> | <JSON value function> |
|||
6.4 |
|||
::My concern is that the current translations of these articles in other languages are based on extremely old stub versions (the German translation referenced isn't even about Null (SQL)). Since there are already translations, I think it would be a good idea to get them in line with this one at least. Unfortunately I only know one language, and I barely know that one :) Thanks. [[User:SqlPac|SqlPac]] 03:18, 26 June 2007 (UTC) |
|||
<unsigned value specification> ::= <unsigned literal> | <general value specification> |
|||
<general value specification> ::= <host parameter specification> | <SQL parameter reference> | <dynamic parameter specification> | <embedded variable specification> | <current collation specification> | CURRENT_CATALOG | CURRENT_DEFAULT_TRANSFORM_GROUP | CURRENT_PATH | CURRENT_ROLE | CURRENT_SCHEMA | CURRENT_TRANSFORM_GROUP_FOR_TYPE <path-resolved user-defined type name> | CURRENT_USER | SESSION_USER | SYSTEM_USER | USER | VALUE |
|||
5.3 ( <literal> Function: Specify a non-null value.) |
|||
== caption == |
|||
<unsigned literal> ::= <unsigned numeric literal> | <general literal> |
|||
<general literal> ::= <character string literal> | <national character string literal> | <Unicode character string literal> | <binary string literal> | <datetime literal> | <interval literal> | <boolean literal> |
|||
7.1 |
|||
The caption for the stylized can and the omega coin had some really weaselly wording. I've changed it, but I'm still not quite happy. It presently reads "The Greek lowercase omega (ω) character used to represent Null in relational databases." Of course, in a relational database, a bunch of bits actually represent null -- not a Greek letter. This is because computers can't read Greek. What's a better way to word this caption so that it isn't so misleading? -- [[User:Mikeblas|Mikeblas]] 02:48, 15 August 2007 (UTC) |
|||
<row value constructor predicand> ::= <common value expression> | <boolean predicand> | <explicit row value constructor> |
|||
6.28 |
|||
:What's even worse, this wording suggests that omega does not represent Null any more. Now fixed to match the article text. [[User:GregorB|GregorB]] 23:31, 31 October 2007 (UTC) |
|||
<common value expression> ::= <numeric value expression> | <string value expression> | <datetime value expression> | <interval value expression> | <user-defined type value expression> | <reference value expression> | <collection value expression> |
|||
:::::: No <null specification> found above for the <comparison predicate>! |
|||
== "Null is different from an empty string and the numerical value 0" == |
|||
:::::: Instead the <null predicate> is used: |
|||
Maybe it ''should'' be this way, but e.g. for Oracle it isn't: see http://www.techonthenet.com/oracle/questions/empty_null.php. This, of course, may lead to all kinds of unpleasant surprises. Perhaps this should be addressed in the article. [[User:GregorB|GregorB]] 14:56, 9 November 2007 (UTC) |
|||
8.8 <null predicate> |
|||
:On a side note: it's not clear to me whether Oracle's behavior breaks rule #3 of [[Codd's 12 rules]], as I vaguely remember reading somewhere that Codd himself said it was OK to treat empty strings as Nulls. Any references on Codd's stance, one way or the other? [[User:GregorB|GregorB]] 15:03, 9 November 2007 (UTC) |
|||
Function |
|||
::Not sure what Codd decided on treating empty strings as nulls, but the ANSI standard specifies that they're not to be treated the same. Codd eventually changed his mind on nulls and stated that multiple null values should be supported. Fortunately for us this wasn't widely adopted. [[Special:Contributions/67.100.13.18|67.100.13.18]] ([[User talk:67.100.13.18|talk]]) 17:32, 19 December 2007 (UTC) |
|||
Specify a test for a null value. |
|||
Format |
|||
<null predicate> ::= <row value predicand> <null predicate part 2> |
|||
<null predicate part 2> ::= IS [ NOT ] NULL |
|||
:::::: When it comes to assignment, e.g. <code>UPDATE</code>, the <null specification> can be used (as a part of <contextually typed value specification>, but not of <value expression> - which includes literals etc.): |
|||
== Are Nulls "necessary"? == |
|||
14.15 |
|||
<update source> ::= <value expression> | <contextually typed value specification> |
|||
6.5 |
|||
The answer is, of course, ''no'': every database schema with nullable columns can be transformed into a logically equivalent database schema without ''any'' nullable columns. This is fairly evident, yet many people still believe that one cannot design an arbitrary database without nullable columns. (Of course, there are performance implications in doing this, but these are purely ''physical'', not logical issues.) |
|||
<contextually typed value specification> ::= <implicitly typed value specification> | <default specification> |
|||
<implicitly typed value specification> ::= <null specification> | <empty specification> |
|||
<null specification> ::= NULL |
|||
<value expression> ::= <common value expression> | <boolean value expression> | <row value expression> |
|||
I think it's important, and I'd incorporate something to that effect in the article, but unfortunately I can't find it referenced anywhere... [[User:GregorB|GregorB]] 14:19, 1 December 2007 (UTC) |
|||
<common value expression> ::= <numeric value expression> | <string value expression> | <datetime value expression> | <interval value expression> | <user-defined type value expression> | <reference value expression> | <collection value expression> |
|||
:::::: It's possible that I have missed to include some parts of interest. But as you have the SQL-2011 specification available, you can perhaps check it out by yourself, or ask me to do it. |
|||
:Nulls are not "necessary", but to eliminate them completely means adopting a model consistent with Darwen's and Date's Sixth Normal Form, which essentially is a primary key + one column per table. Note that eliminating nulls completely requires eliminating outer joins including left outer joins, right outer joins, and full outer joins, since they produce nulls in the result set. This can be accomplished in SQL by using inner joins and the UNION operator. The complexity of the code grows exponentially with the number of joins and "optional" columns involved. So while nulls are not technically "necessary", eliminating them completely from SQL is probably not the best idea. Here's an article at SQL Server Central: http://www.sqlservercentral.com/articles/Advanced/2921/. [[Special:Contributions/67.100.13.18|67.100.13.18]] ([[User talk:67.100.13.18|talk]]) 17:42, 19 December 2007 (UTC) |
|||
:::::: Conclusion: the <code>NULL</code> keyword is not interchangeable with the null value. The <code>NULL</code> keyword can not syntactically be an argument to a comparison. |
|||
::Exactly right! Darwen and Date dislike nulls (to put it mildly), but it makes little sense to push them out of the scheme only to reintroduce them again through outer joins. If there is a practical solution around this, I haven't seen it. Even if one could solve the performance issues (by somehow dissociating the physical model from the logical model, as suggested by Fabian Pascal), the sheer amount of joins looks unappealing. (Which may be a problem with SQL itself, not necessarily the relational model!) Still: when one thinks about relational modeling, it is always good to know that nulls are avoidable; it is only a matter of cost and benefit. [[User:GregorB|GregorB]] ([[User talk:GregorB|talk]]) 20:50, 19 December 2007 (UTC) |
|||
:::::: I know that many, but not all, dbms implementations cheat and allow the <code>= NULL</code> syntax. That's why I made my first edit (Revision as of 20:26, 31 May 2017, which was reverted.) [[User:Fjerdingen|Fjerdingen]] ([[User talk:Fjerdingen|talk]]) 20:40, 8 June 2017 (UTC) |
|||
== Dubious tag == |
|||
I ran across the "dubious" tag in the article by accident, and I fixed the offending text. Now it specifies that Null is not the same as an empty string or the numeric value zero per the ISO SQL standard. This should eliminate any question. If it's important to add vendor-specific null-handling options and features that stray from the standard, then someone might want to consider adding that; however, we tried to describe the ISO standard definitions and behavior for the article. In addition to the Oracle handling of empty string/null equality, there is Microsoft SQL Server's ANSI_NULL setting which changes NULL comparison behavior, and all other vendor-specific items that stray from the standard. It's a slippery slope, however, and you could end up with an article that consists of huge lists of vendor-specific features. We found this out early on and decided that it wasn't useful to include lists of vendor-specific features since they add very little value to the article, but consume a lot of vertical space. Also, whenever someone adds a tag like the "dubious" tag to an article, please mention it on the talk page so we can address your issues quickly. Thanks. [[Special:Contributions/67.100.13.18|67.100.13.18]] ([[User talk:67.100.13.18|talk]]) 18:04, 19 December 2007 (UTC) |
|||
: |
@Fjerdingen: You have conviced me that "= NULL" is illegal in the sense of the SQL standard. I have changed the article. (Maybe that I will do some more changes in the near future.) Thank's for your investigation. --[[User:Kelti|Kelti]] ([[User talk:Kelti|talk]]) 16:18, 20 June 2017 (UTC) |
||
==GA Reassessment== |
|||
::I'm glad the clarification is acceptable. In the section two sections above this one there's no mention that a "dubious" tag was added or where. I found out the tag was added by accident. I ask only that when someone adds a "dubious" or other tag questioning content that they please say so in their comments and possibly post a copy-n-paste of the sentence(s) in question so they can be located and corrected quickly. Thanks. [[Special:Contributions/69.116.243.84|69.116.243.84]] ([[User talk:69.116.243.84|talk]]) 05:35, 25 December 2007 (UTC) |
|||
{{Wikipedia:Good article reassessment/Null (SQL)/1}} |
Latest revision as of 13:07, 8 January 2024
This is the talk page for discussing improvements to the Null (SQL) article. This is not a forum for general discussion of the article's subject. |
Article policies
|
Find sources: Google (books · news · scholar · free images · WP refs) · FENS · JSTOR · TWL |
Archives: 1Auto-archiving period: 12 months |
Null (SQL) was one of the Engineering and technology good articles, but it has been removed from the list. There are suggestions below for improving the article to meet the good article criteria. Once these issues have been addressed, the article can be renominated. Editors may also seek a reassessment of the decision if they believe there was a mistake. | |||||||||||||||||||||||||
|
This article is rated C-class on Wikipedia's content assessment scale. It is of interest to the following WikiProjects: | ||||||||||||||||||
|
To-do list for Null (SQL):
|
I think this is duplicate page
[edit]Why has this page been written? In SQL, null has exactly the same meaning / behavior it has in ALL programming languages. The only thing that COULD (but not in my opinion) justify this page, would have been some discussion about UNKNOWN pseudo-value. But there is nothing like that, so this page is misleading for someone who doesn't know that null is. — Preceding unsigned comment added by 151.21.9.214 (talk) 14:37, 5 October 2011 (UTC)
Amazing and amusing contradictions
[edit]CJ Date says the SQL 3VL logic is none of those usually studied. John Grant says it's Kleene's K3. Thalheim and Schewe say it's Łukasiewicz L3. They can't all be right. What's more amusing is that they disagree about such basic facts. Tijfo098 (talk) 19:44, 7 November 2012 (UTC)
- Actually, this mystery has been solved, cf [1] p. 9. Basically it's both K3 and L3 because they differ only in their table for implication, but SQL has no such operand. And in this sense, SQL's is also an incompletely specified logic. So all of them (Date, Grant and Thalheim & Schewe) are correct! Tijfo098 (talk) 16:25, 8 November 2012 (UTC)
Claimed inconsistency of boolean variables
[edit]The catch is that in implementations which distinguish boolean NULL from Unknown, like in that of Microsoft, an expression like TRUE OR NULL (e.g. "(1 <> 2) OR NULL") fails the type check, so you can't speak of its result, because it's a parsing error. Unless someone can produce a reliable source for this so-called inconsistency, I'll delete the claim. Tijfo098 (talk) 15:31, 8 November 2012 (UTC)
- I've deleted it. Tijfo098 (talk) 16:17, 10 November 2012 (UTC)
Nulls are Equal ?
[edit]The chapter "When two nulls are equal: ..." seems to be outdated. Actually it reads: "Because SQL:2003 defines all Null markers as being unequal to one another...". The content of the chapter is not incorrect, but the actual standard uses more elegant expressions - with a slightly different semantic. SQL:2011 defines in 4.1.5 Properties of distinct the rule "Two null values are not distinct.". Is this only a problem of linguistic representation within the wiki-article or is it a progress in the SQL standard, which is not reflected by the article? As far as I know, the semantic of GROUP BY and other related language elements hasn't changed from 2003 to 2011. --Kelti (talk) 19:57, 11 May 2014 (UTC)
Where are nulls explicitly used?
[edit]I think this article needs to illustrate a real-world example where nulls are explicitly used, so we can see their use (if a use exists). I used SQL a little in work for 8 months, never needed to assign a NULL value to any field (but it was only 8 months of light SQL work after all).
If there are legit uses of explicit nulls, then we should probably NOT get rid of them. If we did, then anyone who needed them would just add an extra boolean field called NULL after every normal field, and thus use them again. The difference would be, they would have to implement the logic themselves and thus reinvent the wheel. If I'm not mistaken, I believe there are flags in a table that you can set to ensure that no field can ever be NULL, and if you really fear/hate NULLS you can do things safely this way.
Also, the "Closed World" argument was good, but in the real world, sometimes certain things are unknown. Again, a real-world example is needed and unfortunately I can't think of one myself due to my limited SQL experience. DrZygote214 (talk) 00:33, 18 April 2015 (UTC)
- Regarding "I believe there are flags in a table that you can set to ensure that no field can ever be NULL", see the section Check constraints and foreign keys in the article, starting at "In order to constrain a column to reject Nulls..." For example, to specify that a user registered with a website must have a handle, the following can be used in the user table definition:
username varchar(255) not null unique
- They are also important in the event that a record has missing fields. Instead of recording default values for such fields, the field is marked as "null", which basically signifies it is empty. (See also Nulls in the Oracle Database SQL Reference.) AS noted in the criticism section of this article. a null value signifies that there is no value assigned for that field, not that the field has a value of 0 (zero) or some such. Mindmatrix 00:58, 18 April 2015 (UTC)
- In real life "I don't know yet" is often the answer to a question. Even a Yes/No question has 3 possible answers (not 2): "Yes" (I know the fact and it is yes), "No" (I know the fact and it is no), and "I don't know yet" (I don't know the fact yet because it is to be determined). And with numbers, the same is true. For example, for each day, enter the weather data, such what the high temperature was. Let's say that in 2013, there were 2 days, July 23 and July 24, when no thermometer reading was recorded. Should you just say that the high temperature that day was zero degrees? Damn, that's funny, the average temperature in July 2013 was strangely cool! No, it wasn't—you just didn't know how to design your database.
This is a basic aspect of information science, and information technology should be designed to handle it, which is why database design and database theory have the null concept. Another way to say it is that a value of zero or no is not the same thing as the lack of a value. In other words, zero and no are information, whereas null is the lack of information, or, more precisely, a piece of meta-information that informs about a lack of information (or metadata about a lack of data). Going months or years without having to consider that distinction is a reflection of the application—that is, of the particular project being worked on: it was evidently a context in which every relevant piece of information was already known at the time of data entry. But in other applications (other projects), one may not be so lucky. It is quite true that a field property can be set to "required" (which means "no nulls"). But whether any particular database is reasonably able to use (or ought not to use) that property on any particular field is determined by the subject matter at hand. See the "July 23 and July 24" example. It should not be about each person's "fear of nulls" or reinvention of the null concept. The concept is already well known.
Related: The critique of the null concept did not really belong in this article's lede. It belongs in the article body. The only people who can properly understand its importance or context are people who have high database theory and database design knowledge. Computer science PhD types. Not me, not the average reader of Wikipedia. As evidenced by the question posed in this talk thread, that is not the average user of this Wikipedia article, and the discussion as currently placed and written in the lede was probably confusing more people than it is helping. I moved it down. First tell a database 101 student what null is and what it's good for. Discuss the advanced philosophical critique later, in a section that 101 students will skip over. — ¾-10 17:35, 18 April 2015 (UTC)
- In real life "I don't know yet" is often the answer to a question. Even a Yes/No question has 3 possible answers (not 2): "Yes" (I know the fact and it is yes), "No" (I know the fact and it is no), and "I don't know yet" (I don't know the fact yet because it is to be determined). And with numbers, the same is true. For example, for each day, enter the weather data, such what the high temperature was. Let's say that in 2013, there were 2 days, July 23 and July 24, when no thermometer reading was recorded. Should you just say that the high temperature that day was zero degrees? Damn, that's funny, the average temperature in July 2013 was strangely cool! No, it wasn't—you just didn't know how to design your database.
- I was going to add, but forgot, the acknowledgment that RDBMSs do have binary field data type (for 0/1, Yes/No, On/Off, True/False), which cannot give 3-value logic including null, but along with that fact comes the corollary that during database design, if that data type is chosen at all, it really should be chosen only for data where it is highly likely (and seems likely to remain likely) that at the time of data entry the person entering the data will always already know what the fact is. Otherwise the designer should use an integer data type that allows 3 options. An example of a website that explores this point is http://allenbrowne.com/noyesno.html. — ¾-10 23:29, 19 April 2015 (UTC)
The part
[edit]The section “Analysis of SQL Null missing-value semantics” seems to provide private Nonsens:
The “groundbreaking work of T. Imielinski and W. Lipski (1984)” was and is completely unknown to the database community.
The proposition “and if is its lifting to a construct intended to represent missing information” has no truth value, thus no content. — Preceding unsigned comment added by 94.219.112.101 (talk) 17:47, 13 May 2015 (UTC)
What is the binary representation of Null?
[edit]What exactly does a SQL engine put "on disk" when it writes Null into a column? Example, if I have a column of type INTEGER, all values between binary "00000000 00000000 00000000 00000000" and "11111111 11111111 11111111 1111111" can be stored, each representing an integer number. If this column also allows "Null" as a value, what exactly is written onto the disk? There's nothing left that can mean "Null", because any bit sequence already stands for some INTEGER value. --194.231.113.66 (talk) 13:29, 17 July 2015 (UTC)
- The field in the database is not assigned a value in the case of NULL; it is empty. (This is the typical case, though some DB may be implemented differently.) See also the discussion Where are nulls explicitly used? above. Mindmatrix
- I would hazard that this is implementation specific. Even a given SQL implementation may have multiple engines (e.g. Maria has, for example innodb) which may be implemented in multiple architectures.
- All the best: Rich Farmbrough, 21:36, 21 November 2016 (UTC).
NULL, Null, or null
[edit]I would suggest that "null" is used for the general concept and "NULL" the SQL implementation. While a lot of the SQL I write uses Null or null, it seems that listing a table with a null field will show NULL. This makes it the "natural" representation in SQL. Unless anyone has a better idea? All the best: Rich Farmbrough, 21:39, 21 November 2016 (UTC).
- The ISO SQL specification (ISO/IEC 9075-2:2016(E)) uses upper case for the
NULL
keyword, and lower case for the null value, but never Null. Fjerdingen (talk) 20:46, 8 June 2017 (UTC)
NULL
[edit]Several examples, some said to be ANSI/ISO SQL compliant, have syntax with = NULL
- which is syntactically invalid. Those examples need to be corrected! Fjerdingen (talk) 20:21, 31 May 2017 (UTC)
- In according to the SQL standard as well as to our SQL wikibook the expression
col_x = NULL
is syntactically correct and leads to the boolean valueUNKNOWN
. As long as someone thinks in the usual two-value-logic of many programming languages, this behaviour may be confusing. In opposite to such languages SQL allows the NULL marker as a legal 'content' of columns and defines rules how to evaluate expressions with the literal NULL (or NULL markers in the column of a row) to true/false/unknown. If someone searches rows containing the NULL marker, the expressioncol_x IS NULL
shall be used: it leads toTRUE
for all rows containing the NULL marker. --Kelti (talk) 08:10, 2 June 2017 (UTC)- When it comes to confirming ISO/ANSI SQL syntax compliance I use the Mimer SQL-2003 Validator (http://developer.mimer.com/validator/parser200x/index.tml). If you run the query
SELECT * FROM T WHERE C = NULL
the result is "syntax error: NULL correction: <identifier>". However, this is the SQL-2003 standard, and there are later revisions of the ISO/ANSI SQL standard. Can you give me a reference that the syntax later has become valid? Fjerdingen (talk) 19:33, 5 June 2017 (UTC)- a) The syntax of the standard did not change over time. Since the very first days "= null" was syntactically correct - and often missleads people. But Mimer seems to change their behaviour. Their SQL-92 validator http://developer.mimer.se/validator/parser92/index.tml#parser gives a different answer to the example. I interpret the Mimer answer in their 200x parser as a hint because this query NEVER returns any row.
- b) Please read the standard. SQL:2011, part-2, page 33 says "... any comparison involving the null value or an Unknown truth value will return an Unknown result.". Kelti (talk) 08:06, 6 June 2017 (UTC)
- It seems like we are discussing two different things here. I am talking about the
NULL
keyword, the syntactical element. Your SQL-2011 reference is about the Null value. Of course the Null value can be used when doing a comparison. However, this doesn't mean theNULL
keyword is syntactically allowed.
- It seems like we are discussing two different things here. I am talking about the
- When it comes to confirming ISO/ANSI SQL syntax compliance I use the Mimer SQL-2003 Validator (http://developer.mimer.com/validator/parser200x/index.tml). If you run the query
- However, the syntax has been changed. As you pointed out,
= NULL
was syntactically correct in SQL-92. But between SQL-92 and SQL-99, the ISO SQL standardization committee worked hard on cleaning up old sins. One of those things were the= NULL
syntax - which was removed! (Once in a while things get deprecated and removed from the SQL standard, e.g.select null from table
, ordinal position in ORDER BY clause, joins without theSELECT
keyword etc.)Fjerdingen (talk) 19:28, 7 June 2017 (UTC)
- However, the syntax has been changed. As you pointed out,
- Your distinction between the
NULL
keyword and anull value
in a column (regarding to the SQL syntax) is new to me. SQL:2011, part-1, page 14 says: "... Every data type includes a special value, called the null value, sometimes denoted by the keyword NULL." Isn't this a statement that both are interchangeable? Kelti (talk) 07:42, 8 June 2017 (UTC)
- Your distinction between the
- The
NULL
keyword is a <null specification> according to the ISO SQL specification (<null specification> ::= NULL
). (And the value of a <null specification> is the null value.) The <null specification> is not a part of the <row value predicand>, which is the argument to a comparison (<comparison predicate>). Below is some copy and paste from the SQL 2016 spec (ISO/IEC 9075-2:2016(E)), sorry for the ruined formatting:
- The
8.2 <comparison predicate> ::= <row value predicand> <comparison predicate part 2> <comparison predicate part 2> ::= <comp op> <row value predicand> <comp op> ::= <equals operator> | <not equals operator> | <less than operator> | <greater than operator> | <less than or equals operator> | <greater than or equals operator>
7.2 <row value predicand> ::= <row value special case> | <row value constructor predicand> <row value special case> ::= <nonparenthesized value expression primary>
6.3 <nonparenthesized value expression primary> ::= <unsigned value specification> | <column reference> | <set function specification> | <window function> | <nested window function> | <scalar subquery> | <case expression> | <cast specification> | <field reference> | <subtype treatment> | <method invocation> | <static method invocation> | <new specification> | <attribute or method reference> | <reference resolution> | <collection value constructor> | <array element reference> | <multiset element reference> | <next value expression> | <routine invocation> | <row pattern navigation operation> | <JSON value function>
6.4 <unsigned value specification> ::= <unsigned literal> | <general value specification> <general value specification> ::= <host parameter specification> | <SQL parameter reference> | <dynamic parameter specification> | <embedded variable specification> | <current collation specification> | CURRENT_CATALOG | CURRENT_DEFAULT_TRANSFORM_GROUP | CURRENT_PATH | CURRENT_ROLE | CURRENT_SCHEMA | CURRENT_TRANSFORM_GROUP_FOR_TYPE <path-resolved user-defined type name> | CURRENT_USER | SESSION_USER | SYSTEM_USER | USER | VALUE
5.3 ( <literal> Function: Specify a non-null value.) <unsigned literal> ::= <unsigned numeric literal> | <general literal> <general literal> ::= <character string literal> | <national character string literal> | <Unicode character string literal> | <binary string literal> | <datetime literal> | <interval literal> | <boolean literal>
7.1 <row value constructor predicand> ::= <common value expression> | <boolean predicand> | <explicit row value constructor>
6.28 <common value expression> ::= <numeric value expression> | <string value expression> | <datetime value expression> | <interval value expression> | <user-defined type value expression> | <reference value expression> | <collection value expression>
- No <null specification> found above for the <comparison predicate>!
- Instead the <null predicate> is used:
8.8 <null predicate> Function Specify a test for a null value. Format <null predicate> ::= <row value predicand> <null predicate part 2> <null predicate part 2> ::= IS [ NOT ] NULL
- When it comes to assignment, e.g.
UPDATE
, the <null specification> can be used (as a part of <contextually typed value specification>, but not of <value expression> - which includes literals etc.):
- When it comes to assignment, e.g.
14.15 <update source> ::= <value expression> | <contextually typed value specification>
6.5 <contextually typed value specification> ::= <implicitly typed value specification> | <default specification> <implicitly typed value specification> ::= <null specification> | <empty specification> <null specification> ::= NULL
<value expression> ::= <common value expression> | <boolean value expression> | <row value expression> <common value expression> ::= <numeric value expression> | <string value expression> | <datetime value expression> | <interval value expression> | <user-defined type value expression> | <reference value expression> | <collection value expression>
- It's possible that I have missed to include some parts of interest. But as you have the SQL-2011 specification available, you can perhaps check it out by yourself, or ask me to do it.
- Conclusion: the
NULL
keyword is not interchangeable with the null value. TheNULL
keyword can not syntactically be an argument to a comparison.
- Conclusion: the
- I know that many, but not all, dbms implementations cheat and allow the
= NULL
syntax. That's why I made my first edit (Revision as of 20:26, 31 May 2017, which was reverted.) Fjerdingen (talk) 20:40, 8 June 2017 (UTC)
- I know that many, but not all, dbms implementations cheat and allow the
@Fjerdingen: You have conviced me that "= NULL" is illegal in the sense of the SQL standard. I have changed the article. (Maybe that I will do some more changes in the near future.) Thank's for your investigation. --Kelti (talk) 16:18, 20 June 2017 (UTC)
GA Reassessment
[edit]The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.
- Article (edit | visual edit | history) · Article talk (edit | history) · Watch • • GAN review not found
- Result: Delisted. ~~ AirshipJungleman29 (talk) 13:07, 8 January 2024 (UTC)
This 2007 listing contains significant uncited material, including whole subsections, violating GA criterion 2b). ~~ AirshipJungleman29 (talk) 20:17, 19 December 2023 (UTC)
- I could probably look into this over the coming weeks. Sohom (talk) 09:19, 21 December 2023 (UTC)
- Hi Sohom Datta, do you still intend to work on this? No worries if not. ~~ AirshipJungleman29 (talk) 18:08, 30 December 2023 (UTC)
- I do intend to, got sidetracked by Site isolation :) Sohom (talk) 19:49, 30 December 2023 (UTC)
- On second thoughts, I'm probably not gonna be working on this. A lot of the text wrt to the article is from 2008, and seems to be non-trivial to source to a source that was online in 2008 and is still online today, while keeping WP:CIRCULAR at bay :( Sohom (talk) 12:24, 8 January 2024 (UTC)
- Sorry to hear that. ~~ AirshipJungleman29 (talk) 13:07, 8 January 2024 (UTC)
- On second thoughts, I'm probably not gonna be working on this. A lot of the text wrt to the article is from 2008, and seems to be non-trivial to source to a source that was online in 2008 and is still online today, while keeping WP:CIRCULAR at bay :( Sohom (talk) 12:24, 8 January 2024 (UTC)
- I do intend to, got sidetracked by Site isolation :) Sohom (talk) 19:49, 30 December 2023 (UTC)
- Hi Sohom Datta, do you still intend to work on this? No worries if not. ~~ AirshipJungleman29 (talk) 18:08, 30 December 2023 (UTC)