This has got to be the most obfuscating articles I have ever read on SQL. Never mind the goofy asides and the non-linear progression. The SQL is also poorly designed and, in some places, dead wrong! Now, how is someone supposed to learn anything about SQL like that? I'm an educator, teaching VB.Net, C++ and SQL, and I have to undo the confusion wrought by bad SQL articles like this one.
Errors: in this code snippet, the author uses no less that three different names for his composite table!
t2_t3_composite_table
t1_t2_composite_table
t2_te_composite_table
Select *
>From t1
left outer join
(select *
from t2,t3
where (any where clause involving t1, t2, t3)
) t2_t3_composite_table on t1_t2_composite_table.f1 = t1.f1
,t4
,t5
,t6
Where (any clause involving t1, t2_te_composite_table, t4, t5, t6)
In addition, the author mixes ANSI 92 SQL Join syntax and old style syntax in the same statement. Anyone learning SQL using this guide is guaranteed to get Cartesian products for tables 4, 5 and 6 following his example, because he does not point out the capital importance of joining all tables by their keys with ON clauses (unless you specifically want a Cartesian product). I can hear the calls to the DB admins now. "Uhh, this query is hung up." Well, yeah, you're returning 10^12 rows.
Compare this article to http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html, and you will see how muck easier SQL is to explain when we proceed stepwise, with clear, simple, logical examples.
|
Christian,
I apologize for the confusion and perhaps the asides that you have referred to in your post. I sincerely hope my explanation here will address some of the issues raised and also puts the article in a better perspective. I have thought long and hard about publishing this article not because the content is not unworthy of it but the format and the intent of the article.
I don't publish articles very lightly. I have published over 20 articles at O'Reilly some fairly involved and some geared towards understanding a certain aspect of a programming language. In addition I have worked with Oracle, SQLServer, Microsoft Access, and SQLServer over the years, mostly from an application programmer (Java, C++, C#) perspective.
The article that was presented here is based on various things that I had to learn outside of what the standard books on SQL teaches. I want to go beyond the basics and point out some of the patterns I have found in using Joins. I have not put as much emphasis in a COMPLETE treatment of any given subject such as the ANSI syntax nor the OPTIMIZATION but more on the ability of patternizing JOINs to retrieve a sensible collection results from databases.
I wanted to document the results that I have found for myself and my coprogrammers that have to deal with databases on and off.
I have also debated about the name of the article. One name I have tossed around was "Notes of Java Programmer on SQL Joins". One of my thoughts when I have published the article was that each of the principles will be a starting point for programmers to further delve into the details and come up with conclusions and further notes.
Finally I hoped the article to be mainly a source of IDEAS to further explore what is possible with SQL when used as a supporting asset of a procedural/oo languages.
Some of the readers have pointed out some errors in the SQL. I apologize for these errors. I did have the article reviewed by a couple of DBAs. Sorry that these have slipped through.
Also importantly thank you for your emphasis and alert on the ANSI 92 SQL along with a fine reference to the joins article on O'Reilly.
Author: Satya Komatineni