Jump to content

Talk:Null (SQL): Difference between revisions

Page contents not supported in other languages.
From Wikipedia, the free encyclopedia
Browse history interactively
← Previous editNext edit →
Content deleted Content added
VisualWikitext
Fjerdingen (talk | contribs)
140 edits
= NULL is invalid syntax
Line 382: Line 382:
==NULL, Null, or null==
==NULL, Null, or null==
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&nbsp;the&nbsp;best: ''[[User:Rich Farmbrough|Rich]]&nbsp;[[User talk:Rich Farmbrough|Farmbrough]]'',<small> 21:39, 21 November 2016 (UTC).</small><br />
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&nbsp;the&nbsp;best: ''[[User:Rich Farmbrough|Rich]]&nbsp;[[User talk:Rich Farmbrough|Farmbrough]]'',<small> 21:39, 21 November 2016 (UTC).</small><br />


== = NULL ==

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)

Revision as of 20:21, 31 May 2017

WikiProject iconComputing GA‑class Mid‑importance
WikiProject iconThis article is within the scope of WikiProject Computing, a collaborative effort to improve the coverage of computers, computing, and information technology on Wikipedia. If you would like to participate, please visit the project page, where you can join the discussion and see a list of open tasks.ComputingWikipedia:WikiProject ComputingTemplate:WikiProject ComputingComputing
GAThis article has been rated as GA-class on Wikipedia's content assessment scale.
MidThis article has been rated as Mid-importance on the project's importance scale.
WikiProject iconDatabases (inactive)
WikiProject iconThis article is within the scope of WikiProject Databases, a project which is currently considered to be inactive.DatabasesWikipedia:WikiProject DatabasesTemplate:WikiProject DatabasesDatabases
Good articleNull (SQL) has been listed as one of the Engineering and technology good articles under the good article criteria. If you can improve it further, please do so. If it no longer meets these criteria, you can reassess it.
Article milestones
DateProcessResult
May 11, 2007Good article nomineeListed
May 17, 2007Peer reviewReviewed
May 22, 2007Featured article candidateNot promoted
November 6, 2008Good article reassessmentKept
Current status: Good article

Re-wrote

I fleshed out this article for you a bit. Enjoy.SqlPac 03:09, 23 March 2007 (UTC)[reply]

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.SqlPac 15:44, 28 March 2007 (UTC)[reply]
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. --Stolze 19:20, 8 May 2007 (UTC)[reply]
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? SqlPac 16:09, 9 May 2007 (UTC)[reply]

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.SqlPac 18:23, 2 April 2007 (UTC)[reply]

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. --Stolze 15:00, 3 April 2007 (UTC)[reply]
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.SqlPac 21:07, 9 April 2007 (UTC)[reply]
I can agree to that. :-) --Stolze 21:31, 14 April 2007 (UTC)[reply]
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 :)SqlPac 02:37, 16 April 2007 (UTC)[reply]

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.SqlPac 02:43, 16 April 2007 (UTC)[reply]

Peer Review Feedback (Automated)

We have some 'automated' feedback from the peer review request. Here are all of the recommendations from Automated Peer Review Results made by Ruhrfisch:

Null (SQL)

============

The following suggestions were generated by a semi-automatic javascript program, and might not be applicable for the article in question.

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. --Stolze 19:19, 8 May 2007 (UTC)[reply]
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. SqlPac 16:06, 9 May 2007 (UTC)[reply]
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. SqlPac 00:26, 17 May 2007 (UTC)[reply]
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. HausTalk 00:46, 12 May 2007 (UTC)[reply]
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. SqlPac 01:15, 17 May 2007 (UTC)[reply]
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. --Stolze 09:34, 19 May 2007 (UTC)[reply]

You may wish to browse through User:AndyZ/Suggestions for further ideas. Thanks, Ruhrfisch 03:17, 5 May 2007 (UTC)[reply]

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. --Stolze 19:15, 8 May 2007 (UTC)[reply]

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. SqlPac 16:04, 9 May 2007 (UTC)[reply]

I have Date's 3rd Manifesto in front of me and his explanation of the following proscription looks like it answers this reference question: Begin quote RM PROSCRIPTION 4: NO NULLS D shall include no concept of a "relation" in which some "tuple" includes some "attribute" that does not have a value.

By definition, tuples, and therefore relations, do not contain nulls (nulls are not values!). SQL, however, does permit nulls in its tables-yet another reason why SQL tables are not true relations. In the Manifesto, by contrast, nulls are absolutely, categorically, and unequivocally outlawed (and so too therefore is n-valued logic for any n > 2). End quote

Date references a book by Codd for his definition of Null, "SQL nulls are not really values at all but "marks." See E.F. Codd: The Relational Model for Database Management Version 2. Reading, Mass.: Addison-Wesley (1990) --Philippe_Perrault 10:10, 2 Jul 09

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. SqlPac 21:10, 10 May 2007 (UTC)[reply]

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. HausTalk 12:45, 11 May 2007 (UTC)[reply]

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 NULL) 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 SqlPac 13:47, 11 May 2007 (UTC)[reply]
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. HausTalk 14:28, 11 May 2007 (UTC)[reply]
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. SqlPac 14:36, 11 May 2007 (UTC)[reply]

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. HausTalk 15:04, 11 May 2007 (UTC)[reply]

FA Review

Some comments from the 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. SqlPac 18:26, 17 May 2007 (UTC)[reply]

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. SqlPac 19:37, 17 May 2007 (UTC)[reply]
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. – Quadell (talk) (random) 20:06, 17 May 2007 (UTC)[reply]
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 SqlPac 20:13, 17 May 2007 (UTC)[reply]
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 :) SqlPac 20:13, 17 May 2007 (UTC)[reply]
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" :) SqlPac 02:52, 19 May 2007 (UTC)[reply]
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. --Stolze 09:24, 19 May 2007 (UTC)[reply]
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. SqlPac 21:22, 19 May 2007 (UTC)[reply]
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. ;-) --Stolze 13:29, 20 May 2007 (UTC)[reply]
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 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. SqlPac 19:27, 20 May 2007 (UTC)[reply]

POLICY DEBATE: Use of source code and other examples in articles

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:

I have opened 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!

Please reply there, not here, if you wish to contribute.—greenrd 10:50, 18 May 2007 (UTC)[reply]

Thanks.SqlPac 15:05, 18 May 2007 (UTC)[reply]

I added my comments in the other discussion. --Stolze 09:14, 19 May 2007 (UTC)[reply]

Wiki Giveth, Wiki Taketh Away?

It appears that the article has now been submitted for Featured Article Review, where debate is held concerning whether an article should be removed from 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. SqlPac 03:55, 28 May 2007 (UTC)[reply]

Oops, FAC failed. My motivation to do anything on here has taken a serious hit over the past week. Oh well. SqlPac 04:14, 30 May 2007 (UTC)[reply]
Sorry for the headache, but you can resubmit this to FAC at any time. Gimmetrow 08:23, 30 May 2007 (UTC)[reply]
No thank you, not interested at all. SqlPac 19:29, 6 June 2007 (UTC)[reply]

Suggested improvements

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. Marskell 09:31, 29 May 2007 (UTC)[reply]

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. SqlPac 19:21, 29 May 2007 (UTC)[reply]
Made recommended changes. Thanks! SqlPac 20:40, 29 May 2007 (UTC)[reply]

Mistake

Raul has 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. Marskell 07:37, 30 May 2007 (UTC)[reply]

GimmeBot and Gimmetrow will straighten out the articlehistory on the talk page. SandyGeorgia (Talk) 08:09, 30 May 2007 (UTC)[reply]
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. Gimmetrow 08:23, 30 May 2007 (UTC)[reply]
Thanks Marskell, but I'm not really interested in going through any of this "process" again. Let someone else deal with these big FAckers. SqlPac 23:46, 30 May 2007 (UTC)[reply]

Explanation of removal of programming language

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.

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. SqlPac 01:27, 8 June 2007 (UTC)[reply]
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.
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. SqlPac 01:26, 10 June 2007 (UTC)[reply]

Conversion to other languages

There were three language tags on the page: 

[[de:Nullwert]]

[[pt:Null (SQL)]]

[[ru:NULL (SQL)]]

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! SqlPac 00:42, 22 June 2007 (UTC)[reply]

English: Please help us translate the updated article at en:Null (SQL).

Russian: Пожалуйста помогите нам перевести updated статью на en:Null (SQL).

Portuguese: Ajude-nos por favor traduzir o artigo updated em en:Null (SQL).

Spanish: Ayúdenos por favor a traducir el artículo actualizado en en:Null (SQL).

German: Helfen Sie uns bitte, den aktualisierten Artikel neu zu übersetzen en:Null (SQL).

French: Svp aidez-nous à traduire l'article mis à jour à en:Null (SQL).

Please excuse the translations - courtesy of Babelfish. SqlPac 00:54, 22 June 2007 (UTC)[reply]

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. --Stolze 00:05, 26 June 2007 (UTC)[reply]
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. SqlPac 03:18, 26 June 2007 (UTC)[reply]

caption

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? -- Mikeblas 02:48, 15 August 2007 (UTC)[reply]

What's even worse, this wording suggests that omega does not represent Null any more. Now fixed to match the article text. GregorB 23:31, 31 October 2007 (UTC)[reply]

"Null is different from an empty string and the numerical value 0"

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. GregorB 14:56, 9 November 2007 (UTC)[reply]

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? GregorB 15:03, 9 November 2007 (UTC)[reply]
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. 67.100.13.18 (talk) 17:32, 19 December 2007 (UTC)[reply]

Are Nulls "necessary"?

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

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... GregorB 14:19, 1 December 2007 (UTC)[reply]

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/. 67.100.13.18 (talk) 17:42, 19 December 2007 (UTC)[reply]
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. GregorB (talk) 20:50, 19 December 2007 (UTC)[reply]
I don't personally put a lot of stock in what Pascal has to say about anything these days. Darwen and Date, in their description of 6NF, at least have the decency to admit it's not a very workable solution within the current generation of SQL DBMS software or server hardware. Pascal makes no such admission, and regularly beats people over the head for using nulls even though it's often the "best tool for the job". Before I get in hot water, let me say that by "best" I'm only referring to the areas of 1) performance, 2) ease and speed of writing code, 3) manageability, 4) resource utilization. I'm sure 6NF and other null-avoidance techniques work extremely well on paper when used on academic hypothetical database management systems. 69.116.243.84 (talk) 04:51, 26 December 2007 (UTC)[reply]

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. 67.100.13.18 (talk) 18:04, 19 December 2007 (UTC)[reply]

Umm... I added the tag and provided a comment two sections above this one; you commented further, apparently. Your clarification is fine: the statement in the article is now clearly true (ISO SQL). Whether vendor-specific deviations are worth mentioning here is anyone's guess; the article is big enough as it is... GregorB (talk) 20:58, 19 December 2007 (UTC)[reply]
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. 69.116.243.84 (talk) 05:35, 25 December 2007 (UTC)[reply]
You're correct, my comment made no mention of the tag, and it's not exactly obvious that the two are connected - sorry about that... GregorB (talk) 19:44, 26 December 2007 (UTC)[reply]

Boolean simplification section

Not sure what the point is to this section? SQL introduced a boolean data type at one point, but I believe it's been rescinded, hasn't it? One of the main problems with it was Null propagation. In the standard that introduced the boolean data type, it specifically states that Null boolean values will be treated as False. For any vendor that actually implemented the boolean data type, if they implemented it according to the standard, I don't think the examples in this section would cause an issue. The issue of Null-propagation and other problems with implementing a boolean data type might make a good addition for this section. SqlPac (talk) 06:08, 26 December 2007 (UTC)[reply]

As I gather, the section deals with boolean algebra, not with the boolean data type. The example was
SELECT * FROM stuff WHERE x OR NOT x;
but it might as well be
SELECT * FROM stuff WHERE x = 0 OR NOT (x = 0);
Same thing, no? GregorB (talk) 19:56, 26 December 2007 (UTC)[reply]
The first sentence of the section states the following:

"A WHERE expression, such as the following, might at first glance seem to be subject to simplification using boolean algebra (presuming that x is of boolean datatype):"

With this statement, it seems proper to assume that the writer intended readers to believe that x is used to represent a variable of the boolean datatype, no?
If x in the example does not stand for a Boolean data type variable, then the example is confusing at best and a better example would be given with a fully expressed compound predicate as you have done above (WHERE x = 0 OR NOT(x = 0)), and of course replacing the text in the introductory sentence to make it less misleading. I believe the details of SQL 3VL are explained in detail in the article, including the intricacies of the Unknown truth value, so this example seems superfluous as well as confusing.
BTW, the link to boolean datatype in the first sentence links to a Wikipedia article that doesn't even discuss SQL-defined truth values, but rather actual Boolean data type instances as represented in other programming languages. SqlPac (talk) 22:30, 27 December 2007 (UTC)[reply]
I just updated the boolean datatype article to include a discussion of the sadly doomed SQL Boolean datatype.SqlPac (talk) 23:07, 27 December 2007 (UTC)[reply]
Also added another section on the inconsistency introduced by SQL Boolean datatypes due to null propagation. Enjoy.SqlPac (talk) 01:55, 28 December 2007 (UTC)[reply]
A clarification: when I said "the section deals with boolean algebra" I meant that the section essentially illustrates that the law of excluded middle does not hold, which is a more general statement that is true irrespective of datatypes involved - as long as x from the original example is a boolean expression, of course. (And by the way: good job with the Boolean datatype inconsistency section!) GregorB (talk) 23:58, 28 December 2007 (UTC)[reply]
That makes more sense, and is a very good point. I think that section should probably be revised to discuss that, with boolean expressions (like in your example) to replace the boolean variables, and with a discussion of how it pertains to the law of the excluded middle. As it stands now it just sort of summarizes the previous discussion of SQL 3VL in a somewhat confusing manner. (Glad you like the Boolean data type section - a lot of people don't even realize SQL:1999 introduced Boolean data type, and a lot of other crap that seems to have been dropped relatively quickly! SQL:1999 is like the black sheep of the SQL standards family...)SqlPac (talk) 06:49, 30 December 2007 (UTC)[reply]
Yes: 3VL implies "law of the excluded middle does not hold" - no need to discuss it twice. (What is worse, it's not immediately obvious that the Boolean simplification section discusses the same thing. The example that is given there is useful, though.) GregorB (talk) 12:21, 7 January 2008 (UTC)[reply]

Improvement or not? 

SELECT * FROM stuff;
-- This is (because of 3VL) equivalent to:
SELECT * FROM stuff WHERE ( x = 10 ) OR NOT ( x = 10 ) OR x IS NULL;

COALESCE function

Why did they name the COALESCE function "COALESCE" ? - Bevo (talk) 20:31, 14 September 2010 (UTC)[reply]

Little bit confusing about inner/cross joins

Inner joins and cross joins, also available in standard SQL, do not generate Null placeholders for missing values in related tables.

Surely this is accurate, but kind of an indirect way of explaining what is happening - it eliminates the rows where there are missing values as it is joining these tables, and thus any nulls that would have been needed with an outer join between the same dataset aren't needed. --81.151.99.87 (talk) 21:40, 26 May 2011 (UTC)[reply]

NVL used, but not explained

NVL is used in an explanation of another function, but itself is not explained. --193.169.48.48 (talk) 11:22, 15 June 2011 (UTC)[reply]

I think this is duplicate page

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)[reply]

Amazing and amusing contradictions

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)[reply]

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)[reply]

Claimed inconsistency of boolean variables

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)[reply]

Nulls are Equal ?

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)[reply]

Where are nulls explicitly used?

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)[reply]

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)[reply]
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)[reply]
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)[reply]

The part

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)[reply]

What is the binary representation of Null?

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)[reply]

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).[reply]

NULL, Null, or null

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).[reply]


= NULL

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)[reply]

Retrieved from "https://en.wikipedia.org/enwiki/w/index.php?title=Talk:Null_(SQL)&oldid=783209209"