Talk:Null (SQL)/Archive 1

Archive 1

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)

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)

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)

I can agree to that. :-) --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 :)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.SqlPac 02:43, 16 April 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. --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. SqlPac 16:04, 9 May 2007 (UTC)

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

Re-wrote

I fleshed out this article for you a bit. Enjoy.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.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. --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? SqlPac 16:09, 9 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. Haus^Talk 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 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)

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. Haus^Talk 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. 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. Haus^Talk 15:04, 11 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. SqlPac 21:10, 10 May 2007 (UTC)

• Sounds good to me. – Quadell ^{(talk) (random)} 00:52, 11 May 2007 (UTC)
  • Done. Might want to take a look to make sure it still flows properly. Thanks.SqlPac 02:41, 12 May 2007 (UTC)

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)

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

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

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.

• Consider adding more links to the article; per Wikipedia:Manual of Style (links) and Wikipedia:Build the web, create links to relevant articles.^[?]
  • Added a lot more links. SqlPac 03:44, 6 May 2007 (UTC)
• This article has no images. Please see if there are any 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-fair use images on the Wikimedia Commons, go to commons:special:upload.^[?]
  • I don't know of any applicable images? Any suggestions out there folks? Thanks. SqlPac 03:44, 6 May 2007 (UTC)
• See if possible if there is a free use image that can go on the top right corner of this article.^[?]
  • I don't know of any applicable images here either? Any suggestions guys 'n gals? Thanks. SqlPac 03:44, 6 May 2007 (UTC)

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)

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)

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)

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. Haus^Talk 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. 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. --Stolze 09:34, 19 May 2007 (UTC)

• There may be an applicable infobox for this article. For example, see Template:Infobox Biography, Template:Infobox School, or Template:Infobox City.^[?] (Note that there might not be an applicable infobox; remember that these suggestions are not generated manually)
  • I couldn't find an applicable infobox, but that doesn't mean there's not one. Anyone out there know if there is one that would fit? Thanks. SqlPac 03:44, 6 May 2007 (UTC)
    • There's no applicable infobox for this article currently. SqlPac 15:17, 11 May 2007 (UTC)

• Per Wikipedia:Context and Wikipedia:Build the web, years with full dates should be linked; for example, link January 15, 2006.^[?]
  • Added links to dates in article. SqlPac 03:44, 6 May 2007 (UTC)
• 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 ==Magellan's journey==, use ==Journey==.^[?]
  • Changed article headings to match Wikipedia:Manual of Style (headings) requirements. SqlPac 03:44, 6 May 2007 (UTC)
• Please reorder/rename the last few sections to follow guidelines at Wikipedia:Guide to layout.^[?]
  • Re-ordered final sections to match Wikipedia:Guide to layout. SqlPac 03:44, 6 May 2007 (UTC)
• Watch for redundancies that make the article too wordy instead of being crisp and concise. (You may wish to try Tony1's redundancy exercises.)
  • Vague terms of size often are unnecessary and redundant - “some”, “a variety/number/majority of”, “several”, “a few”, “many”, “any”, and “all”. For example, “All pigs are pink, so we thought of a number of ways to turn them green.”
    • Did a quick run-through, but didn't do a thorough check for this one yet. 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. SqlPac 15:28, 11 May 2007 (UTC)
• Avoid using contractions like (outside of quotations): isn't.
  • Didn't do a thorough check yet. SqlPac 03:44, 6 May 2007 (UTC)
  • Fixed by Haus^Talk SqlPac 15:15, 11 May 2007 (UTC)
• Please ensure that the article has gone through a thorough copyediting so that it exemplifies some of Wikipedia's best work. See also User:Tony1/How to satisfy Criterion 1a.^[?]
  • We'll see what we can do. SqlPac 03:44, 6 May 2007 (UTC)

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

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

Sorry for the headache, but you can resubmit this to FAC at any time. Gimmetrow 08:23, 30 May 2007 (UTC)

No thank you, not interested at all. SqlPac 19:29, 6 June 2007 (UTC)

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)

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)

Made recommended changes. Thanks! SqlPac 20:40, 29 May 2007 (UTC)

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)

GimmeBot and Gimmetrow will straighten out the articlehistory on the talk page. 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. Gimmetrow 08:23, 30 May 2007 (UTC)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)